Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheet names
Hi,
I'm having trouble working out how to do this: Loop through all worksheets in a workbook, returning the sheet names to a range on the first sheet, i.e. On sheet 1, cell A1 contains the name of the first worksheet, cell A2 contains the name of the second worksheet etc. Number of sheets is unknown. Also to return the total number of sheets to cell B1. Any thoughts? Cheers, Tom. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheet names
Try this to list names to the active sheet.
Sub listall() x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws End Sub Mike "mr tom" wrote: Hi, I'm having trouble working out how to do this: Loop through all worksheets in a workbook, returning the sheet names to a range on the first sheet, i.e. On sheet 1, cell A1 contains the name of the first worksheet, cell A2 contains the name of the second worksheet etc. Number of sheets is unknown. Also to return the total number of sheets to cell B1. Any thoughts? Cheers, Tom. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheet names
Fantastic.
Just the job. Tom. "Mike" wrote: Try this to list names to the active sheet. Sub listall() x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws End Sub Mike "mr tom" wrote: Hi, I'm having trouble working out how to do this: Loop through all worksheets in a workbook, returning the sheet names to a range on the first sheet, i.e. On sheet 1, cell A1 contains the name of the first worksheet, cell A2 contains the name of the second worksheet etc. Number of sheets is unknown. Also to return the total number of sheets to cell B1. Any thoughts? Cheers, Tom. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
List worksheet names
Sorry i missed the bit about the number of sheets. Add this line just before
the End Sub Cells(1, 2).Value = x - 1 "Mike" wrote: Try this to list names to the active sheet. Sub listall() x = 1 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets Cells(x, 1).Value = ws.Name x = x + 1 Next ws End Sub Mike "mr tom" wrote: Hi, I'm having trouble working out how to do this: Loop through all worksheets in a workbook, returning the sheet names to a range on the first sheet, i.e. On sheet 1, cell A1 contains the name of the first worksheet, cell A2 contains the name of the second worksheet etc. Number of sheets is unknown. Also to return the total number of sheets to cell B1. Any thoughts? Cheers, Tom. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Referencing worksheet names from a list?? | Excel Discussion (Misc queries) | |||
Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
List of worksheet names | Excel Discussion (Misc queries) | |||
want a list of selectable names on my worksheet | Excel Worksheet Functions |