Extract Sheet Names

[venumkd]

Sirs,

I need a method or VBA to extract or list sheet names of a workbook into a worksheet.

The workbook contains around 5000 sheets named not in a particular order. These sheet names are to be extracted / entered or listed to Sheet1 of the workbook in any of the columns preferably in Column A.

Thanks & Regards.

VENU

 

[Peter_SSs]

Try

Sub List_Worksheets()
  Dim ws As Worksheet
  Dim i As Long
  
  For Each ws In Worksheets
    i = i + 1
    Sheets("Sheet1").Range("A" & i).Value = ws.Name
  Next ws
End Sub

 

[sandy666]

with Power Query

let
    Source = Excel.Workbook(File.Contents("full_path_to_your_file"), null, true),
    TSC = Table.SelectColumns(Table.SelectRows(Source, each ([Kind] = "Sheet")),{"Name"})
in
    TSC

 

[Rick Rothstein]

5000 worksheets! Really, 5000 of them? I cannot imagine what your tabs must look like. Anyway, although I haven't tested it on such a huge number of worksheet, I think it should work... you might find my "Neat Go To Sheet" selector to be of some interest. Here is a link to my mini-blog article where I introduced it...

A Neat "Go To Sheet" Selector
.

 

[KevCarter]

5000 worksheets! Really, 5000 of them? I cannot imagine what your tabs must look like. Anyway, although I haven't tested it on such a huge number of worksheet, I think it should work... you might find my "Neat Go To Sheet" selector to be of some interest. Here is a link to my mini-blog article where I introduced it...

A Neat "Go To Sheet" Selector
.

Some very nice code Rick. Thank you for sharing, I'll learn a lot from it. Cheers!

 

[venumkd]

Sirs,
It is working on VBA.
I do not know "A Neat "Go To Sheet" Selector"
VBA is enough.
Thanks a lot.
VENU

 

[Peter_SSs]

You're welcome.