# VBA to dynamically add new sheets before x number of sheets



## muhleebbin (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

Thank you sooo much Z51!  That did the trick!


----------

