{"id":69,"date":"2011-10-10T16:50:38","date_gmt":"2011-10-10T16:50:38","guid":{"rendered":"http:\/\/www.edcortes.ph\/?p=163"},"modified":"2021-07-11T08:00:02","modified_gmt":"2021-07-11T08:00:02","slug":"getting-started-with-vba-in-excel-2010-part-3","status":"publish","type":"post","link":"https:\/\/emcortes.com\/?p=69","title":{"rendered":"Getting Started with VBA in Excel 2010 (Part 3)"},"content":{"rendered":"
\n

Suppose that you have a workbook that contains lists on a large number of worksheets and that you want to change the name of each worksheet to match the heading of the list on that worksheet. Not every worksheet has a list on it, but if it does, the heading is in cell B1, and if it does not, cell B1 is blank. The names of worksheets without lists should be left alone.<\/p>\n

Ordinarily, this could be a complex task that involves looking at each worksheet to see if it has a list, copying the name if it does, clicking the worksheet tab, and then pasting in the new name. Instead of performing all of those steps manually, use Excel VBA to rename the sheets automatically.<\/p>\n

Learning about Objects<\/h4>\n
\n

The first step is to find out how to manipulate the particular objects that you need to work with to accomplish your task; for example, worksheets, worksheet names, cells, and cell contents. In Excel, there are at least two ways to approach the problem:<\/p>\n

    \n
  • Go directly to the Object Model Reference.<\/li>\n
  • Record some of the actions that you want to automate, see how the recorded code manipulates the objects, and then go to the Object Model Reference for more information.<\/li>\n<\/ul>\n

    Opinions vary on which approach is preferable, but for now, try using the Macro Recorder first.<\/p>\n

    Using the Macro Recorder<\/h4>\n
    \n

    Sometimes a simple recorded macro is all you need; in these cases, you do not even have to look at the code. More often, recording alone is not enough. Instead, it is a starting point in the following process.<\/p>\n

    To use the Macro Recorder as a starting point to your solution<\/h3>\n
    \n
      \n
    1. Record the actions that you want to code.<\/li>\n
    2. Review the code and find the lines that perform those actions.<\/li>\n
    3. Delete the rest of the code.<\/li>\n
    4. Modify the recorded code.<\/li>\n
    5. Add variables, control structures, and other code that the Macro Recorder cannot record.<\/li>\n<\/ol>\n<\/div>\n

      Begin your investigation by recording a macro that renames a worksheet to New Name<\/strong>. You can then use the recorded macro to develop your own macro that renames multiple worksheets based on their contents.<\/p>\n

      To record a macro that renames a worksheet<\/h3>\n
      \n
        \n
      1. Click Record Macro<\/strong> on the Developer<\/strong> tab.<\/li>\n
      2. Name the macro RenameWorksheets<\/strong>, rename Sheet1 to New Name<\/strong>, and click Stop Recording<\/strong>.<\/li>\n
      3. Go to the Developer<\/strong> or View<\/strong> tab, click the Macros<\/strong> button, and choose Edit<\/strong> to open the Visual Basic Editor.<\/li>\n<\/ol>\n<\/div>\n

        The code in the Visual Basic Editor should look similar to the following.<\/p>\n