Define a Sheets Array dynamically

How can I define this array dynamically?

Sheets(Array("AAL Wk 40", "ACA Wk 40", "AFR Wk 40", "BAW Wk 40", "BWA Wk 40", _
        "ETD Wk 40", "GIA Wk 40", "LAN Wk 40", "LOT Wk 40", "OAS Wk 40", "RJA Wk 40", _
        "SVA Wk 40", "TAR Wk 40")).Copy

The optimum solution would be something like this:
Sheets(Array(Sheets(1) through Sheets(Sheets.Count - 1)).copy  - OR -  
Sheets(Array(Sheets(1).Name & ":" & Sheets(Sheets.Count - 1).Name).copy

Do you know what I mean?

gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?
krishnakrkcConnect With a Mentor Commented:
Sheets(Application.Transpose(Evaluate("Row(1:" & Sheets.Count - 1 & ")"))).Copy

Open in new window

Martin LissOlder than dirtCommented:
Is there anything unique about the sheets that you want to include in the array? Also what do you want to do with the array?
NorieConnect With a Mentor VBA ExpertCommented:
Perhaps something like this.
Dim arrSheets()
Dim I As Long

    For I = 1 To Sheets.Count - 1
        ReDim Preserve arrSheets(1 To I)
         arrSheets(I) = Sheets(I).Name
    Next I

Open in new window

gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
Thanks, Kris. Nothing gratifies me VBA-wise like a compact code, and you can't beat one line! inmorie, yours also works quite well, but I have to give Kris the bulk of the points because of the one line thing. :- )

Thanks to both of you,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.