Solved

Define a Sheets Array dynamically

Posted on 2014-11-04
4
59 Views
Last Modified: 2014-11-05
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?

Thanks,
John
0
Comment
Question by:gabrielPennyback
4 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40423139
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?
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 100 total points
ID: 40423150
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
    
   Sheets(arrSheets).Copy

Open in new window

0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 400 total points
ID: 40423424
Sheets(Application.Transpose(Evaluate("Row(1:" & Sheets.Count - 1 & ")"))).Copy

Open in new window


Kris
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 40424619
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,

John
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question