VBA to dynamically add new sheets before x number of sheets

[muhleebbin]

I have the following code where it will add an upcoming years set of sheets after x number of sheets:

Sub AddUOSheets()
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    Set wSh = ActiveSheet
    Set wBk = ActiveWorkbook
   
    Application.ScreenUpdating = False
    For Each xRg In wSh.Range("A1:A5")
        With wBk
            .Sheets.Add Before:=Sheets(Sheets.Count - 144)
            On Error Resume Next
            ActiveSheet.Name = xRg.Value
            If Err.Number = 1004 Then
              Debug.Print xRg.Value & " already used as a sheet name"
            End If
            On Error GoTo 0
        End With
    Next xRg
    Application.ScreenUpdating = True
   
    Sheets("UO Sheets Adder").Activate
End Sub

Right now the code has a static number 144 in the line:

.Sheets.Add Before:=Sheets(Sheets.Count - 144)

Is there a way to make this dynamic so that it will add 5 to the previous number after the macro is used? After the next time the macro is used the line should then read:

.Sheets.Add Before:=Sheets(Sheets.Count - 149)

Thanks in advance for your assistance!

 

[Z51]

Perhaps have a hidden sheet that simply contains a starting # (e.g. 144) in cell A1, then reference and add to it every time the macro is run. Perhaps something like:

.Sheets.Add Before:=Sheets(Sheets.Count - Sheets("YourHiddenSheetName").Range("A1").Value)

    Next xRg
    Sheets("YourHiddenSheetName").Range("A1").Value = Sheets("YourHiddenSheetName").Range("A1").Value + 5
    Application.ScreenUpdating = True

 

[muhleebbin]

Thank you sooo much Z51! That did the trick!