How to get the current sheet number of a workbook?
When working with workbooks containing multiple worksheets, it is often useful to know the exact position or index number of the current (active) sheet. This is particularly helpful for creating references, automating tasks, or simply for keeping track of your data organization, especially in complex workbooks where the number of sheets can be large. Instead of manually counting each sheet to find its position, there are several convenient methods to directly retrieve the current worksheet’s number in Excel.
Get the current worksheet number using a simple formula
Get the current worksheet number of a workbook with VBA code
Get the current worksheet number using a simple formula
To display the index number of the current worksheet, you can use Excel’s built-in SHEET function. It quickly returns the sheet’s position within the workbook, offering a simple and reliable alternative to the older GET.WORKBOOK method.
This solution works well in most scenarios where you need to reference or display the index of a sheet within the workbook, such as for dynamic dashboards, sheet navigation systems, or simply as visible documentation for users.
To show the current sheet number directly in your worksheet, enter the following formula in any cell:
="Current sheet index = " & SHEET()After pressing Enter, Excel will display the sheet’s index number. For example, if you place the formula on the third worksheet in your workbook, the result will read: Current sheet index = 3
Advantages: This method requires no macros or special setup, and updates automatically when you move or rename worksheets. It’s the simplest and safest way to reference the sheet’s position within your workbook.
Get the current worksheet number of a workbook with VBA code
One direct approach to obtaining the index number of the active worksheet is by using a simple VBA code. This method is especially suitable if you are comfortable with macros and want a quick, dialog-based solution.
To use the VBA solution, follow the steps below:
1. Activate the worksheet for which you wish to retrieve the index number.
2. Press ALT + F11 to open the Microsoft Visual Basic for Applications window.
3. In the VBA editor, click Insert > Module, then paste the following code into the module window.
VBA code: Get the current sheet number
Sub GetActiveSheetIndex()
MsgBox ActiveSheet.Index
End Sub
4. Press the F5 key to execute the code. Once run, a message box will appear showing the index number of the worksheet you have activated, as illustrated in the screenshot below. If you change to another worksheet and rerun the script, the sheet number will update accordingly.
Related articles:
How to quickly insert sheet names in cells in Excel?
How to count the number of sheets of a workbook?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in