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.