VBA: How to copy worksheet number to new worksheet and increment worksheet number by 1

[VBA Challenged]

In the same workbook, I want to copy an active worksheet to a new worksheet and name the new worksheet with the previous named worksheet and increment that name (number) by 1. The worksheet name is a four digit number (it is also a year). The first worksheet is named 2021. When I run the macro, it copies the contents of the 2021 worksheet into a new worksheet and applies the name of 2022. If I run the macro again, then the new worksheet is titled 2022 (2), instead of 2023. You will notice that my code is treating the worksheet name as a date. I did this because I didn't know how to code it with numbers. Either way is acceptable to me. Need help such that the worksheet names are 2022, 2023, 2024, etc. Here is the VBA code that I am using:

Sub Macro5()
'
    On Error Resume Next
    ActiveSheet.Select
    ActiveSheet.Copy After:=ActiveSheet
    ActiveSheet.Name = Format(DateAdd("y", 1, Date), "YYYY")
    If Err.Number <> 0 Then ActiveSheet.Activate
        
 End Sub

 

[offthelip]

this should do it:

Sub Macro5()
'
    Dim tmpr As Long
'    On Error Resume Next
    tempr = ActiveSheet.Name
    If IsNumeric(tempr) Then
    tempr = tempr + 1
    ActiveSheet.Copy After:=ActiveSheet
    ActiveSheet.Name = tempr
    End If
 End Sub

 

[VBA Challenged]

this should do it:

Sub Macro5()
'
    Dim tmpr As Long
'    On Error Resume Next
    tempr = ActiveSheet.Name
    If IsNumeric(tempr) Then
    tempr = tempr + 1
    ActiveSheet.Copy After:=ActiveSheet
    ActiveSheet.Name = tempr
    End If
 End Sub

Your solution worked exactly as I needed. Thanks very much for your assistance.