L
Legacy 64414
Guest
Hi guys!
I would really appreciate your help on this one. It seems simple but...
I've made a macro that takes an existing sheet (named after a month of the year), and copies it into another one, cleans up the data entry slots and renames it to next month's name. Problem is I don't know how to get the macro to recognize the current sheet's name as a month and then name the new one with the next month.
For example, I have a current sheet named "August". When I activate my macro, I get a new sheet that's all cleaned up but named "August (2)" but I need it to be named "September", and I need this for all the months of the year.
I tried to record the naming to September in the macro, but of course, it tries to name them all September each time I run the macro, so that doesn't work.
Here is what my macro looks like (I've put the problem lines in red):
Private Sub CommandButton1_Click()
'
ActiveSheet.Unprotect
Sheets("August").Select
Sheets("August").Copy Before:=Sheets(1)
Sheets("August (2)").Select
Sheets("August (2)").Name = "September"
Range("F7:I7").Select
Selection.ClearContents
Range("F10:K19").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=12
Range("F21:I21").Select
Selection.ClearContents
Range("F23:K43").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("B49:M59").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-36
Range("F7:I7").Select
End Sub
Can anyone please help?
I would really appreciate your help on this one. It seems simple but...
I've made a macro that takes an existing sheet (named after a month of the year), and copies it into another one, cleans up the data entry slots and renames it to next month's name. Problem is I don't know how to get the macro to recognize the current sheet's name as a month and then name the new one with the next month.
For example, I have a current sheet named "August". When I activate my macro, I get a new sheet that's all cleaned up but named "August (2)" but I need it to be named "September", and I need this for all the months of the year.
I tried to record the naming to September in the macro, but of course, it tries to name them all September each time I run the macro, so that doesn't work.
Here is what my macro looks like (I've put the problem lines in red):
Private Sub CommandButton1_Click()
'
ActiveSheet.Unprotect
Sheets("August").Select
Sheets("August").Copy Before:=Sheets(1)
Sheets("August (2)").Select
Sheets("August (2)").Name = "September"
Range("F7:I7").Select
Selection.ClearContents
Range("F10:K19").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=12
Range("F21:I21").Select
Selection.ClearContents
Range("F23:K43").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("B49:M59").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-36
Range("F7:I7").Select
End Sub
Can anyone please help?