How to access worksheets in Excel using a macro
Worksheets can be accessed using index numbers in a macro An index number is a sequential number assigned to an Excel sheet, based on the position of its sheet tab (counting from the left) among the Excel sheets of the same type. The following procedure uses the Worksheets property to activate worksheet two in the active Excel workbook.
Sub selectsheet2()
Worksheets(2).Activate
End Sub
If you want to work with all types of sheets in Microsoft Excel (worksheets, charts, modules, and dialog sheets), use the Sheets property. The following procedure activates Excel sheet three in the workbook as also demonstrated in the Excel training video.
Sub selectsheet3()
Sheets(3).Activate
End Sub
Implementing the process:
- Click on the Tools menu in the Excel menu bar
- Select Macro and from the pop-up options select Visual Basic
- The Visual Basic Editor window opens. Click on Insert in the menu bar
- Select Module from the drop-down options
- The 2 lines of code preceded with an apostrophe are remarks for the benefit of the user
- Next define a macro name
- The next code line is to access the third worksheet out of the 3 active worksheets
- Run the macro to observe that the third sheet is activated You’ll find such a macro useful when you have to enter data in another worksheet after finishing your work in some other worksheet because the first activity involved is to activate that worksheet and then activate or select a cell.
Note: The index order can change if you move, add, or delete the Excel sheets.
Further reading:
How to: Refer to Sheets by Index Number