How to modify macro for monthly sheets

[GregR]

I have this macro code which does not work

Sub MasterMonthlyCopy()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 11
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Application.GetCustomListContents(3) &
Format(Date, "yy")
Next
Application.ScreenUpdating = True
End Sub

What I am trying to accomplish is copy Sheet(1) 11 times and rename the
copied sheets to Feb06, Mar06 etc. TIA

Greg

 

[Tom Ogilvy]

Sub MasterMonthlyCopy()
Dim i As Integer
Dim sName as String
Dim sh as Worksheet
Application.ScreenUpdating = False
set sh = Activesheet
For i = 1 To 11
sh.Copy After:=Sheets(Sheets.Count)
sName = format(DateSerial(year(date),i+1,1),"mmm")
ActiveSheet.Name = sName & Format(Date, "yy")
Next
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Try this

It copy the sheet named "Sheet1"

If you want the year to be 206 then use this line

ActiveSheet.Name = Format(DateSerial(2005, i + 1, 1), "mmm") & "2006"



Sub MasterMonthlyCopy()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 11
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(DateSerial(2005, i + 1, 1), "mmm") & Format(Date, "yy")
Next
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Sorry, I was only focused on the Month. if you wanted next year

set sh = Activesheet
For i = 1 To 11
sh.Copy After:=Sheets(Sheets.Count)
sName = format(DateSerial(year(date)+1,i+1,1),"mmmyy")
ActiveSheet.Name = sName
Next
Application.ScreenUpdating = True
End Sub

 

[GregR]

Tom and Ron, thank you for your brilliance and infinite patience

Greg

 

[Dave Peterson]

I'm not sure that their patience is infinite, but I have heard it recently went
from 64k to a meg!

(Excel humor!)

 

[Ron de Bruin]

LOL