List of All Workbook's Worksheets

  • Thread starter Thread starter jonoro
  • Start date Start date
J

jonoro

I have a number of workbooks containing umpteen worksheets.
How do I enter, into an "index worksheet", say, a list of all current
worksheet tabs in that workbook, in the order in which they appear?

I'm using Excel 2003 in XP...
 
Hi,

Try this code. which will put the sheet names in column A of the active sheet

Sub marine()
For x = 1 To Worksheets.Count
Cells(x, 1) = Sheets(x).Name
Next
End Sub

Mike
 
This will list all sheets in your workbook:
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

This will do the same, up to 30, and then shift one column over and repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name

iRow = iRow + 1
If iRow > 30 Then
iRow = 2
iCol = iCol + 1
End If

Next

Range("A1").Select
End Sub
 
Thanx for the help...
--
JR


Mike H said:
Hi,

Try this code. which will put the sheet names in column A of the active sheet

Sub marine()
For x = 1 To Worksheets.Count
Cells(x, 1) = Sheets(x).Name
Next
End Sub

Mike
Click to expand...
 
Thanx for the help...
--
JR


ryguy7272 said:
This will list all sheets in your workbook:
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

This will do the same, up to 30, and then shift one column over and repeat:
Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name

iRow = iRow + 1
If iRow > 30 Then
iRow = 2
iCol = iCol + 1
End If

Next

Range("A1").Select
End Sub
Click to expand...
 
Back
Top