Copying Sheet Using Incremental Data From Cell

[Evernight2021]

I'm attempting to create a button that when pressed will copy the current sheet to the end of the sheet list and rename it using some predefined text as well as incremented data from one cell.

The cell is A1 and will contain a year. I want to have the new tab be renamed to 'Schedule 2019' when pressed (pulling the 2019 from cell A1 in the 'Schedule 2018' sheet and incrementing it by 1), and when pressed in the 'Schedule 2019' sheet, create a 'Schedule 2020' sheet and so on.

I have successfully written the code to create the sheet and rename it using just the data from the cell, however I can't seem to figure out how to include the 'Schedule' portion before it. I'm also wondering if it's possible to have the year in A1 automatically increment by one on the copied sheet so the year will be correct.

The code I'm working with now is as follows:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub CommandButton1_Click()
    ActiveSheet.Copy after:=Sheets(Worksheets.Count)
    With ActiveSheet
        .Name = .Range("A1").Value
    End With
End Sub
[/FONT]

Any assistance you can provide will be greatly appreciated!

 

[RSpin]

The first line will add the word Schedule and space, then the value of range A1 +1. The second line will change the value in the cell.

change:

.Name = .Range("A1").Value

to:

.name = "Schedule " & [COLOR=#574123].Range("A1").Value + 1
.range("A1") = .Range("A1") +1

[/COLOR]

 

[mumps]

Try:

Private Sub CommandButton1_Click()
    ActiveSheet.Copy after:=Sheets(Worksheets.Count)
    With ActiveSheet
        .Name = Split(.Range("A1").Value, " ")(0) & " " & Split(Range("A1").Value, " ")(1) + 1
        .Range("A1") = ActiveSheet.Name
    End With
End Sub

 

[Evernight2021]

That works perfectly! Thank you so much!

 

[mumps]

If you are referring to my suggestion, then you are very welcome.