Excel Macro Training

Accessing 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:
  1. Click on the Tools menu in the Excel menu bar
  2. Select Macro and from the pop-up options select Visual Basic
  3. The Visual Basic Editor window opens. Click on Insert in the menu bar
  4. Select Module from the drop-down options
  5. The 2 lines of code preceded with an apostrophe are remarks for the benefit of the user
  6. Next define a macro name
  7. The next code line is to access the third worksheet out of the 3 active worksheets
  8. 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.




Watch the video on youtube
Further reading
Workbooks and worksheets in Excel

Accessing worksheets in Excel using a macro