Solved

# Define a Sheets Array dynamically

Posted on 2014-11-04
61 Views
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
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

LVL 48

Expert Comment

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

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
``````
0

LVL 18

Accepted Solution

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

Kris
0

LVL 1

Author Closing Comment

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

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…
###### Suggested Courses
Course of the Month6 days, 15 hours left to enroll