Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycling through worksheets
Hi,
I'm cycling through the worksheets of a workbook extracting their name. I'm using the following code which is "clunky" - what's a better way of doing it..?? For Each sheet In ActiveWorkbook.Worksheets sheet.Activate my var = ActiveSheet.Name Next sheet thks Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycling through worksheets
Hi Chris,
When you loop through the Worksheets collection, the variable you use in the For Each statement will hold a reference to the current Worksheet in the collection. So you should use that instead of ActiveSheet: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets MsgBox ws.Name Next ws Alternatively, you could get all the Worksheet names into an array if you'd like: Dim asWSNames(1 to 1) As String Dim nSheetCount As Integer Dim nSheetNo As Integer nSheetCount = ActiveWorkbook.Worksheets.Count Redim WSNames(1 to nSheetCount) For nSheetNo = 1 To nSheetCount WSNames(nSheetNo) = ActiveWorkbook.Worksheets(nSheetNo).Name Next nSheetNo -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Chris Gorham wrote: Hi, I'm cycling through the worksheets of a workbook extracting their name. I'm using the following code which is "clunky" - what's a better way of doing it..?? For Each sheet In ActiveWorkbook.Worksheets sheet.Activate my var = ActiveSheet.Name Next sheet thks Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycling through worksheets
With a macro you can do this
It will add a sheet to your workbook and add the sheetnames Sub TabNames() Application.ScreenUpdating = False Dim Nsheet As Worksheet Set Nsheet = Sheets.Add Dim WS As Worksheet Dim r As Integer r = 1 For Each WS In ThisWorkbook.Worksheets If WS.Name < Nsheet.Name Then Nsheet.Range("A" & r) = WS.Name r = r + 1 End If Next WS Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Chris Gorham" wrote in message ... Hi, I'm cycling through the worksheets of a workbook extracting their name. I'm using the following code which is "clunky" - what's a better way of doing it..?? For Each sheet In ActiveWorkbook.Worksheets sheet.Activate my var = ActiveSheet.Name Next sheet thks Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab keeps cycling through the same row | Excel Discussion (Misc queries) | |||
Cycling through workbooks | Excel Discussion (Misc queries) | |||
Anyone out there knows of running cycling and swimming logs? | Excel Discussion (Misc queries) | |||
Open a Read-Only File by cycling thru these 5 passwords | Excel Programming | |||
Need UDF help; cycling through various sheets using VBA name property | Excel Programming |