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.
Code
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
Note:
- 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.
