Solved: Sheets(Array(1,2,3).Copy based on date?

[piercedgeek]

I think this one should be easy, but I'm having trouble finding a way to do it.

I'm trying to copy tabs from a workbook into a new workbook, the catch is, I want to copy different ones each day (MTD stuff), for today I'd use:
Sheets(Array("MTD", "1", "2", "3", "4", "5", "6")).Copy
but how do I make it do 1 through X where X = yesterday?

I know there must be a way easier than making 31 if's that check the date!

Thanks for any help,
Chris

[austenr]

We could use more information. Is there a new sheet added each day? One way I can think of is to set an interior cell color a certain value after you copy the sheet, say cell 65536 in column IV, when your copy routine runs check that cell for that color in each sheet. If its not that color then copy the sheet.

Of course there are many ways to accomplish any task. This is just one but I bet somebody can come up with a better way.

[piercedgeek]

The file already has all of the sheets it will ever need, "MTD" and "1" through "31".
On the 3nd of the month, I want to copy "MTD", "1", and "2" to a new book... on the 4th, "MTD", "1", "2", and "3".. and so on, each day make a brand new book, and include the past days on it.

I guess I could copy them 1 at a time till I hit the current day -1, I was hoping of some way to use Sheets(Array(???)).Copy with the sheet names in place of ???
I won't have a problem finding out what sheet # I need to copy up to, but I can't figure out how to make Sheets(Array(???)).Copy do only the ones I want (and have it change each day)

[Bob Phillips]

Dim arySheets
Dim i As Long

ReDim arySheets(1 To Day(Date))
arySheets(1) = "MTD"
For i = 1 To Day(Date) - 1
arySheets(i + 1) = CStr(i)
Next i
Sheets(arySheets).Copy

[piercedgeek]

Bingo! that's exactly what I needed thank you!

[piercedgeek]

I had a small problem; when running on the 1st, it would only copy "MTD", but we need it to copy MTD and every day from last month.
I made a minor change to the code (applying -1 to the date before changing it into the day number, and adding 1 to that for the ReDim, so there's space for the "MTD" tab) and now it works as intended. I have included the changed code incase anyone else has a use for it:


Dim arySheets
Dim i As Long
ReDim arySheets(1 To Day(Date - 1) + 1)
arySheets(1) = "MTD"
For i = 1 To Day(Date - 1)
arySheets(i + 1) = CStr(i)
Next i
Sheets(arySheets).Copy