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

Excel 359 Views 2 Min Read

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.
Tags: