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

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" wrote:

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

Thanks Mike

"Mike H" wrote:

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" wrote:

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

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

"Vital_ar" wrote in message
...
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