T
tor
Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?
in an excel worksheet?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jim Cone
The free Excel add-in "XL Extras" will do that. It creates a table of contents
of sheet names with each name hyperlinked to the sheet.
The program does other nice stuff including sorting sheets and changing font case.
Download from... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA
"tor" <[email protected]>
wrote in message
Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?
of sheet names with each name hyperlinked to the sheet.
The program does other nice stuff including sorting sheets and changing font case.
Download from... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA
"tor" <[email protected]>
wrote in message
Any way to put together a list of the names of all worksheets in a workbook
in an excel worksheet?
B
Bernard Liengme
The sub below will do what you want
As codes the list starts in E4
Sub mysheets()
myrow = 4
mycolumn = 5
For j = 1 To ActiveWorkbook.Worksheets.Count
Cells(myrow, mycolumn) = Worksheets(j).Name
myrow = myrow + 1
Next
End Sub
best wishes
As codes the list starts in E4
Sub mysheets()
myrow = 4
mycolumn = 5
For j = 1 To ActiveWorkbook.Worksheets.Count
Cells(myrow, mycolumn) = Worksheets(j).Name
myrow = myrow + 1
Next
End Sub
best wishes
G
Gord Dibben
One method is a macro.
Insert a new sheet then run the macro.
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run or edit the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord Dibben MS Excel MVP
Insert a new sheet then run the macro.
Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run or edit the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord Dibben MS Excel MVP
M
Mike
Paste this into a vb module and run. It will put all the worksheet names into
Col A of the active worksheet.
Sub namesheets()
x = 1
Dim sht As Worksheet
For Each sht In Sheets
ActiveSheet.Cells(x, 1) = sht.Name
x = x + 1
Next sht
End Sub
Mike
Col A of the active worksheet.
Sub namesheets()
x = 1
Dim sht As Worksheet
For Each sht In Sheets
ActiveSheet.Cells(x, 1) = sht.Name
x = x + 1
Next sht
End Sub
Mike