VBA: Macro to List all Sheets in a Workbook

September 6th, 2004 | Categories: VBA | Tags:

The following macro loops through every sheet in a workbook and writes the tab name of each sheet sequentially to a sheet you choose. This could be handy for a quick list of every sheet in a workbook with many sheets.

To use the macro just replace the word Sheet1(it appears twice) in the code with the tab name where you would like the results. Make sure there isn’t any important information on the output tab because it clears the data their before writing to 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

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. September 8th, 2008 at 15:00
    Reply | Quote | #1

    Amazing, you saved an incredible amount of time and enabled me to super organize my workbook. Thank you!!!!!

  2. wiz….
    October 23rd, 2008 at 19:29
    Reply | Quote | #2

    super cool stuff!!!! u where the only 1 to have this code in such a simple manner ….hat off to you..

  3. December 9th, 2008 at 16:12
    Reply | Quote | #3

    Great!
    I have been looking for an elegant solution like this!
    Saved me about 2-3 hours of copy pasting…

  4. Chris
    April 29th, 2009 at 07:01
    Reply | Quote | #4

    Thanks,
    Simple, elegant code

  5. October 21st, 2009 at 12:59
    Reply | Quote | #5

    I like it. Very simple. Thanks!

    Arpad

  6. Kris
    April 7th, 2010 at 14:06
    Reply | Quote | #6

    This is useful. What if I need to get the list of all sheets of a workbook in another workbook?

    • Stan
      June 26th, 2010 at 14:20
      Reply | Quote | #7

      Kris :This is useful. What if I need to get the list of all sheets of a workbook in another workbook?

      Using code above: (Thanks for it – very useful)

      Sub ListSheets()

      Dim ws As Worksheet
      Dim x As Integer
      Dim wb1 As String ‘workbook you work from
      Dim wb2 As String ‘workbook you want to get list of sheets from

      wb1 = ActiveWorkbook.Name
      wb2 = “YourBookName.xls” ‘ <<< Change That

      x = 1

      Workbooks(wb1).Sheets("Sheet1").Range("A:A").Clear
      Workbooks(wb2).Activate

      For Each ws In Worksheets

      Workbooks(wb1).Sheets("Sheet1").Cells(x, 1) = ws.Name
      x = x + 1

      Next ws

      Workbooks(wb1).Activate
      Sheets("Sheet1").Select

      End Sub

  7. Erin
    June 9th, 2010 at 22:20
    Reply | Quote | #8

    Thank you so much! You just saved me about two hours of work!

  8. Ariela Grinnell
    November 13th, 2012 at 19:16
    Reply | Quote | #9

    Thank you so much! Worked like a charm!