VBA: Macro to List all Sheets in a Workbook
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
Amazing, you saved an incredible amount of time and enabled me to super organize my workbook. Thank you!!!!!
super cool stuff!!!! u where the only 1 to have this code in such a simple manner ….hat off to you..
Great!
I have been looking for an elegant solution like this!
Saved me about 2-3 hours of copy pasting…
Thanks,
Simple, elegant code
I like it. Very simple. Thanks!
Arpad
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
Thank you so much! You just saved me about two hours of work!
Thank you so much! Worked like a charm!