Cycling through worksheets

[Chris Gorham]

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

 

[Jake Marx]

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]

 

[Ron de Bruin]

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