In Vba, Determine The Number Of The Active Worksheet


June 04, 2021 - by Bill Jelen

In Vba, Determine The Number Of The Active Worksheet

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?
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:

e9781615474011_i0260.jpg

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.



Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.