Trying to creat an array of All sheets in WB

[jim may]

Sub Macro2()
Dim stgSheets As String, Cnt As Long
Dim arrSheets()
Cnt = Sheets.Count
ReDim arrSheets(Cnt - 1)
For i = 1 To Cnt
stgSheets = stgSheets & Sheets(i).Name
arrSheets(i - 1) = stgSheets
'sSheets = sSheets & "", ""
Next i

'stgSheets at this point = "Sheet1Sheet2Sheet3"
'Yet I want to produce "Sheet1","Sheet2",Sheet3" inorder to pass the string
' into the one liner

'Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

End Sub

 

[gaj104]

Does this code point you in the right direction?

Function mySheetsArr()

Dim mySheets() As Variant
Dim i As Long, j As Long

i = ThisWorkbook.Sheets.Count - 1

ReDim mySheets(i)

For j = 0 To i

    mySheets(j) = ThisWorkbook.Sheets(j + 1).Name

Next j

Sheets(mySheets).Select

End Function

Hope it helps.

 

[GTO]

Hi Jim,

At least as you showed, you are wanting all the worksheets. Why not just:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exampleSelectAll()<br>    ThisWorkbook.Worksheets.Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

By chance are you wanting to build a collection of worksheets that discludes some?

Mark