How do I set up 12 months/31 days each dated spreadsheets?

[kayak99]

How do I add tabs and set up 12 months of (31 days each) dated spreadsheets?
I can delete any tabs of shorter months.


Thanks

 

[Khoshravan]

Insert| Worksheet. This will add worksheets and you will see tabs in the
bottom. To speed up process for adding WS, you can press Ctrl+Y, after
inserting 1st WS. Then double click the tabs to change the names.

 

[kayak99]

I tried that earlier and it added the tabs backwards, i.e. 6, 5, 4, 1, 2, 3.

Will that matter when I name them Jan 1, Jan 2, Jan 3, etc and what's the
best way to name those without doing it individually?

Thanks

 

[Khoshravan]

As for first part, click on tab name and keep it a second, a small page icon
will appear, then you can drag it and drop where ever you want. This way you
can change the location of tabs.
As for second part, please give us more detail about what are you going to
do in each day (separate tab). without knowing your plan, it might not be
wise way to produce one sheet per day. Later you might have problems
consolidating data from so many sheets into one sheet for analysis and
summarizing.
But if you insist you have to look for a Macro to do this for you.

 

[Roger Govier]

Hi

I have a sample workbook which does this.
It can be downloaded from
http://www.contextures.com/CreateMthlyWkbks.zip

 

[Dave Peterson]

First, I agree with Gord. Don't do this. You're going to make it much more
difficult to analyze your data.

I'd do my best to put all the information in one worksheet. Add a column for
the date. You'll be able to use pivottables, charts, filters, subtotals...

All that stuff goes away if you separate your data onto different worksheets.

If you have to split it, can you use 12 worksheets and still use a column for
the date/day???

Option Explicit
Sub testme()
Dim FirstDate As Date
Dim LastDate As Date
Dim iDate As Date

FirstDate = DateSerial(2009, 1, 1)
LastDate = DateSerial(2009, 12, 31)

'just for testing, use a smaller finish date
LastDate = DateSerial(2009, 1, 5)

For iDate = LastDate To FirstDate Step -1
Worksheets.Add.Name = Format(iDate, "mmmm dd")
Next iDate

End Sub

ps. If I were doing this, I'd use a format of "yyyy-mm-dd". It would make
sorting the worksheets much easier.

pps. If you wanted to avoid Saturdays and Sundays, you could use something
like:

For iDate = LastDate To FirstDate Step -1
Select Case Weekday(iDate)
Case Is = vbSaturday, vbSunday
'skip it
Case Else
Worksheets.Add.Name = Format(iDate, "mmmm dd")
End Select
Next iDate

as that loop.