Workbook question...

[Maurice]

I need to create a workbook for each month of the year. Each workbook needs
to have a worksheet for each day of the month.

I want to be able to create the worksheet for the 1st of the month and be
able to copy it out 30 times, once for each day. The header of the worksheet
has a date field. I don't want to manually have to update each day of the
month, so how can I copy the sheet for day 1 so that the date chages to the
2nd, 3rd, 4th..... 31th as I copy it.

I hope the question makes sense... any suggestions are appreciated.

 

[smartin]

I need to create a workbook for each month of the year. Each workbook needs
to have a worksheet for each day of the month.

I want to be able to create the worksheet for the 1st of the month and be
able to copy it out 30 times, once for each day. The header of the worksheet
has a date field. I don't want to manually have to update each day of the
month, so how can I copy the sheet for day 1 so that the date chages to the
2nd, 3rd, 4th..... 31th as I copy it.

I hope the question makes sense... any suggestions are appreciated.

I question why you want to disperse your data across 365 worksheets in a
year. This will make a nightmare of summarizing your data. But I suppose
you have your reasons.

Here is one way to do what you ask using VBA. This assumes the first day
of the month is in cell A1. The code will create as many sheets as there
are days in the month, name each sheet accordingly, and place the
appropriate date in cell A1 of each new sheet.

' begin code -------------------------------------
Sub AllDaysAsWorksheets()
Dim d As Date

If Sheets.Count > 1 Then
MsgBox "More than 1 sheet exists. Quitting now."
Else
Application.ScreenUpdating = False
Sheets(1).Activate
d = Range("A1").Value
Sheets(1).Name = Format(d, "yyyymmdd")
d = d + 1
Do
Sheets(1).Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Activate
.Name = Format(d, "yyyymmdd")
End With
Range("A1").Value = d
d = d + 1
Loop Until Day(d) = 1
Application.ScreenUpdating = True
End If
End Sub
' end code ---------------------------------------

If you are not sure how to use this, first call up the VBA editor by
pressing Alt+F11, right click in your project and select Insert | Module
and paste the code above in the blank editing area that appears. Press
Alt+Q or Alt+F11 again to return to the workbook. Now press Alt+F8 to
call up the Run Macro dialog, select "AllDaysAsWorksheets" and click Run.

There is one little safety feature in that the code will not generate
more worksheets if there is already more than one.