Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REMOVING WORKBOOK'S ALL FORMULAS | Excel Discussion (Misc queries) | |||
Can Excel list all of a workbook's worksheets on a worksheet? | Excel Worksheet Functions | |||
easy way to Consolidate Data within workbook's various worksheets | Excel Worksheet Functions | |||
easy way to Consolidate Data within one workbook's various workshe | Excel Worksheet Functions | |||
Function to List an Excel Workbook's Sheet Names | Excel Discussion (Misc queries) |