Excel Training

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



Watch this training video on Youtube

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 links
Referring to Sheets by Name [Excel 2003 VBA Language Reference]
Access worksheets in Excel using names in macros