Grab Excel WorkSheet Names Into 1 Sheet

Here's a nice macro that will combine all your worksheet names into a new sheet.

Some more good excel tips here too plus a cool util called RDBMerge!

Sub ListSheetNames()

Dim NumSheets
NumSheets = Sheets.Count

Application.DisplayAlerts = False
Dim i
For i = 1 To NumSheets + 1
If ActiveSheet.Name = "SheetNames" Then
Sheets("SheetNames").Select
ActiveWindow.SelectedSheets.Delete
Exit Sub
End If
Next i
Application.DisplayAlerts = True

Sheets.Add
ActiveSheet.Name = "SheetNames"
Sheets("SheetNames").Move after:=Sheets(NumSheets + 1)
'MsgBox (NumSheets)

For i = 1 To NumSheets
Range("A" & i) = Sheets(i).Name
Next i

End Sub

Leave a Reply

Your email address will not be published.