How to List all Worksheets in a Workbook in Excel VBA?

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.