In Vba, Determine The Number Of The Active Worksheet

June 04, 2021 - by Bill Jelen

Challenge: You want to refer to the worksheet two sheets to the right of the active worksheet in VBA. How can you figure out the index number of the current worksheet?

Solution: You can figure out the index number of the current worksheet by using ActiveSheet.Index.

Figure 133. How can a macro tell that you are on worksheet 2?

In Figure 133, the active worksheet could be referred to as either Worksheets(2) or Worksheets(“Patient Accounting”). In my VBAbooks and seminars, I tell people that it is better to use Worksheets (“Patient Accounting”). However, in some situations, you might really need to refer to a worksheet with an index number. Perhaps if you needed to refer to a sheet two sheets to the right of the active sheet, you could refer to Worksheets(x+2). Is there an easy way to figure out the index number of the active sheet?

You might figure it out using a brute-force loop:

However, this is the long way around. The Index property of a worksheet identifies the location of the worksheet within the workbook. You could use Worksheets(“Patient Accounting”).Index to return the number 2, or you could simply use ActiveSheet.Index to return the number 2.

Summary: The Index property returns the position of a worksheet in the workbook.

Title Photo: Maksym Kaharlytskyi on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.