copy sheets

[kathy]

I have the following macro that names the tabs in a workbook by the month and
year.. how do I get the contents of sheet1 to copy with this.. rather than
all of them being blank... also I still want the months on the tabs to be dec
to jan left to right rather than Jan to dec left to right..
ideas?


Sub createmonthsheets()
Dim intTemp
Dim dtTemp As Date
dtTemp = "Jan 2010"
ActiveWorkbook.Sheets.Add Count:=(14 - 1 - ActiveWorkbook.Sheets.Count)
For intTemp = 2 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(intTemp).Name = _
Format(DateAdd("m", (intTemp - 2), dtTemp), "mmm-yy")
Next
ActiveWorkbook.Save
End Sub

 

[Jacob Skaria]

Hi Kathy, try the modified one

Sub createmonthsheets()
Dim intTemp As Integer
Dim dtTemp As Date
dtTemp = "Jan 2010"
For intTemp = 1 To 12
Sheets("Sheet1").Copy After:=Sheets(1)
ActiveSheet.Name = Format(DateAdd("m", (intTemp - 1), dtTemp), "mmm-yy")
Next
ActiveWorkbook.Save
End Sub

If this post helps click Yes

 

[kathy]

When I put this into a new work book it works fine but when I put it into an
existing work book I get a run time error # 9.. subscript out of range?
please tell me what that means? Thanks

 

[kathy]

never mind I got it.. my sheet in existing workbook was sheet 12.. when I
renamed it to 1 all worked.. Thanks again