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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
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
 

Forum statistics

Threads
1,141,665
Messages
5,707,701
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top