Refer to Sheets by Name VBA

How to access worksheets in Excel using names in macros

Access worksheets in Excel using their names in macros to make calculations easier, quicker, less error prone and provide better macro code. ¬†You can use the Sheets property in Microsoft Excel to return a worksheet, chart, module, or dialog sheet. The example in the Excel training video activates the sheet named “Expenses” in the active Excel workbook. This is easier for programmers than using ‘sheet1’, ‘sheet2’ , etc. Macro code:

Sub ActivateExpenses()

Sheets(“Expenses”).Activate

End Sub

Or you can use the following VBA code:

Sub accesssheetbyname()

Worksheets(“Expenses”).Activate

End Sub

Implementing the macro:

  1. Click on Tools menu in the Excel menu bar
  2. Select Macro from the drop-down menu and then select the Visual Basic Editor option
  3. In the Visual Basic Editor window click on the Insert menu bar and select Module
  4. In the workspace enter the above code. The first two lines with the apostophes are remarks for the user’s benefit
  5. When you run the macro by clicking on the ‘>’ run button or pressing F5 from the keyboard, the Expenses worksheet will be highlighted which means that it is now active and any data entered will go into its cells

Further reading:

Referring to Sheets by Name [Excel 2003 VBA Language Reference]

Leave a Reply

Your email address will not be published. Required fields are marked *