Solved

Define a Sheets Array dynamically

Posted on 2014-11-04
4
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 48

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 34

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

622 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