Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
REMOVING WORKBOOK'S ALL FORMULAS FARAZ QURESHI Excel Discussion (Misc queries) 3 November 4th 08 12:08 PM
Can Excel list all of a workbook's worksheets on a worksheet? [email protected] Excel Worksheet Functions 2 April 3rd 08 12:14 AM
easy way to Consolidate Data within workbook's various worksheets estimator Excel Worksheet Functions 1 February 20th 06 07:49 PM
easy way to Consolidate Data within one workbook's various workshe estimator Excel Worksheet Functions 1 February 20th 06 02:30 AM
Function to List an Excel Workbook's Sheet Names KymY Excel Discussion (Misc queries) 1 April 1st 05 10:47 AM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"