Trying to Avoid having to update VBA Code every year when the new year comes

[jupasto]

Hello I'm a VBA newb but I managed to patch together some code that works for what I need. When I open the workbook it automatically creates a duplicate worksheet of sheet "2020" and labels it "Scratch[date of cell B1]" (first deleting the previous scratch file). I was wondering if anyone could improve the code so that I don't have to keep changing it every time it's a new year. For instance when 2021 comes around I'll be working with worksheet "2021" so I'll have to manually update the VBA to change the reference from 2020 to 2021.

Sub Workbook_Open()
    Dim sName
    sName = "Scratch" & Month(Range("B1")) _
            & "-" & Day(Range("B1")) _
            & "-" & Year(Range("B1"))
    Worksheets("2020").Copy after:=Sheets(Worksheets.Count)
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Scratch*" Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True
    ActiveSheet.Name = sName
    Worksheets("2020").Activate
 End Sub

Thanks in advance!

 

[Fluff]

How about

Sub Workbook_Open()
    Dim sName
    Dim Yr As String
    Yr = CStr(Year(Date))
    sName = "Scratch" & Month(Range("B1")) _
            & "-" & Day(Range("B1")) _
            & "-" & Year(Range("B1"))
    Worksheets(Yr).Copy after:=Sheets(Worksheets.Count)
    Dim ws As Worksheet
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Scratch*" Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True
    ActiveSheet.Name = sName
    Worksheets(Yr).Activate
 End Sub

 

[offthelip]

Try this code, change this statement:

Worksheets("2020").Copy after:=Sheets(Worksheets.Count)

to

Dim Yr As String
Dim nextyr As String

Yr = Year(Now())
nextyr = Year(Now()) + 1
Worksheets(Yr).Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = nextyr

 

[jupasto]

Thanks a lot Fluff and offthelip!

My problem is solved!

 

[Fluff]

Glad we could help & thanks for the feedback