List of All Workbook's Worksheets
 

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...
--
Thanx.

JR

List of All Workbook's Worksheets
 

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

"jonoro" wrote:

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...
--
Thanx.

JR

List of All Workbook's Worksheets
 

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Mike H" wrote:

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

"jonoro" wrote:

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...
--
Thanx.

JR

List of All Workbook's Worksheets
 

Thanx for the help...
--
JR


"Mike H" wrote:

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

"jonoro" wrote:

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...
--
Thanx.

JR

List of All Workbook's Worksheets
 

Thanx for the help...
--
JR


"ryguy7272" wrote:

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


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Mike H" wrote:

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

"jonoro" wrote:

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...
--
Thanx.

JR