In this post, let’s have a look at a quick tip showing how to list all worksheets in a workbook in Excel workbook using Excel VBA.
How to List all Worksheets in a Workbook in Excel VBA?List all Worksheets in a Workbook in Excel VBA
You can use a macro, which loops through every sheet in the workbook and updates the tab name of each sheet to a Master-sheet. This could be handy for a quick list of every sheet in a workbook with many sheets.
To run the VBA code, first you will need to do the following:
- Under the Developer tab, click Visual Basics.
- Click on the insert in the menu and click the module option.
- Enter the code and run it.
Sub ListSheets() Dim ws As Worksheet Dim x As Integer x = 1 Sheets("Sheet1").Range("A:A").Clear For Each ws In Worksheets Sheets("Sheet1").Cells(x, 1) = ws.Name x = x + 1 Next ws End Sub
- Replace the Sheet 1 with the name of your Master sheet, where you need the list to be updated.
- This code clears the data before it updates the sheet name. So make sure there isn’t any important information on the output tab.
Enter your email address to subscribe to this blog and receive notifications of new posts by email.