How do I name a new sheet created within a macro?

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? :rolleyes: :)
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Private Sub CommandButton1_Click()
'
    With Sheets("August")
        .Unprotect
        .Copy Before:=Sheets(1)
    End With
    With ActiveSheet
        .Name = "September"
        .Range("F7:I7, F10:K19, F21:I21, F23:K43, B49:M59").ClearContents
    End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,040
Messages
5,545,676
Members
410,697
Latest member
srishtijain0708
Top