Dynamic Update between sheets

  • Thread starter Thread starter margolis22
  • Start date Start date
M

margolis22

I have used the macro recorder to assign a hotkey so I can easily
copy the 'last' sheet to a new sheet. The problem I am having is how
to automatically have the script name the new sheet for me. I want
to check the name of the last sheet and name the new sheet the next
month/year. So if my last sheet if "Jan04" I would want the script
to name the next sheet "Feb04" automatically. I also want to see if
this will work for an infinite period of years (when I go from Dec04
to Jan05 it would know, etc...)

Thanks,

JG
 
One way:

Public Sub AddNewMonthSheet()
Dim sNewName As String
With Worksheets
With Worksheets(.Count)
sNewName = Format(DateAdd("m", 1, _
DateValue("1 " & Left(.Name, 3) & " " & _
Right(.Name, 2))), "mmmyy")
End With
.Add(After:=Worksheets(.Count)).Name = sNewName
End With
End Sub
 
Back
Top