Extract the Sheet Name

[Vital_ar]

D/a,
Is there any option like a function or a macro to extract the sheet names in
a excel file.
For Example: I have a Excel file with 10 Sheets names IT1,IT2,IT3,IT4... IT10
I want all these names in a seperate file/sheet in this format
A B C
1 IT1
2 IT2
3 IT3
4 IT4
5 IT5
6 IT6
7 IT7
8 IT8
9 IT9
10 IT10

Thanks in Advance

 

[Mike H]

Hi,

This small macro will put them in column A of the activesheet

Sub Sheet_List()
Dim x As Long
For x = 1 To Worksheets.Count
Cells(x, 1) = Sheets(x).Name
Next
End Sub

Mike

 

[Vital_ar]

Thanks Mike

Hi,

This small macro will put them in column A of the activesheet

Sub Sheet_List()
Dim x As Long
For x = 1 To Worksheets.Count
Cells(x, 1) = Sheets(x).Name
Next
End Sub

Mike

 

[Harald Staff]

Hi

With the file open, run this:

Sub SheetList()
Dim L As Long
Dim WB As Workbook
Dim Osht As Worksheet
Set WB = ActiveWorkbook
Workbooks.Add (1)
DoEvents
Set Osht = ActiveSheet
For L = 1 To WB.Sheets.Count
Osht.Cells(L, 1).Value =L
Osht.Cells(L, 2).Value = WB.Sheets(L).Name
Next
Set WB = Nothing
Set Osht = Nothing
End Sub

HTH. Best wishes Harald