Modifying code so that Month feature updates automatically

[Enzo_Matrix]

Is there a way to modify this so that in March, it will transition automatically to April, April -> May and so forth?

Sub NewMonth()'
' NewMonth Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Sheets(2).Select
    Sheets(2).Copy Before:=Sheets(3)
    Sheets(3).Select
    Sheets(3).Name = "March - On-Time"
    ActiveCell.FormulaR1C1 = "March - Mississauga"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "3/1/2018"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "3/2/2018"
    Range("A4:A5").Select
    Selection.AutoFill Destination:=Range("A4:A23"), Type:=xlFillDefault
    Range("A4:A23").Select
    Range("I2").Select
    ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "C:\Users\jbotha\Desktop\on-time\[Blank template.xlsm]March - On-Time!R3C1:R23C5" _
        , Version:=xlPivotTableVersion15)
    Sheets("Month By Month Comparison").Select
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "='March - On-Time'!R[20]C[2]"
    Range("C6").Select
    Sheets("Feb - On-Time").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[JoeMo]

This will provide the name of the month following the current month

Dim Mnth as string
Mnth = MonthName(Month(Application.EOMonth(Date, 0) + 1))

 

[Enzo_Matrix]

@JoeMo

I am still new to VBA and used the Macro recorder to generate the code listed above. Where would I input your code to make it fit within mine?

 

[JoeMo]

Use the Mnth variable in place of every string where you have "March". For example, this:

Sheets(3).Name = "March - On-Time"

becomes:

Sheets(3).Name = Mnth & " - On-Time"