Multiple copies of worksheet template in same workbook

[wnorrick]

I am setting up a workbook that will have 130 worksheets when finished plus a couple with master data. I would like to set up one template and have one cell contain the property code which is just a 5 digit number. I would like to use vba or something to automaically create the other 129 worksheets and while doing so increase that one cell that contains the property code by 1. So if the first worksheet has 22313 in cell B4 i want the copy of the template to add 1 to that so the second worksheet would have 22314 in B4, next worksheet would have 22315 in B4 and so on for 130 total worksheets.
ter the
The templates pull a lot of data from the master sheet based on vlooup of that one code.
Is there a way to do this without copying the first worksheet over 129 times and entering the correct code each time in B4.
Any help is really appreciated. Thank you

 

[Luke M]

Perhaps something like this?

Sub MakeCopies()
Dim sourceWS As Worksheet
Dim newWS As Worksheet
Dim copyCount As Long
Dim i As Long

'Query user
copyCount = InputBox("How many copies do you want?", "Copy worksheet", 1)
Application.ScreenUpdating = False
Set sourceWS = ActiveSheet
'Make copies
For i = 1 To copyCount
    sourceWS.Copy after:=Worksheets(Worksheets.Count)
    Set newWS = Worksheets(Worksheets.Count)
    'Change cell value
    newWS.Range("B4").Value = newWS.Range("B4").Value + i
Next i
Application.ScreenUpdating = True       
       
End Sub