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
How to List all Worksheets in a Workbook in Excel VBA?

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.
Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.