How to modify macro for monthly sheets
 

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

How to modify macro for monthly sheets
 

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

--
Regards,
Tom Ogilvy

"GregR" wrote in message
oups.com...
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

How to modify macro for monthly sheets
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GregR" wrote in message oups.com...
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

How to modify macro for monthly sheets
 

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"GregR" wrote in message
oups.com...
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

How to modify macro for monthly sheets
 

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

Greg

How to modify macro for monthly sheets
 

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

(Excel humor!)

GregR wrote:

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

Greg

--

Dave Peterson

How to modify macro for monthly sheets
 

LOL

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dave Peterson" wrote in message ...
I'm not sure that their patience is infinite, but I have heard it recently went
from 64k to a meg!

(Excel humor!)

GregR wrote:

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

Greg

--

Dave Peterson