Excel VBA: Insert N number of Sheets based on cell

Question

I'm new in Excel VBA. I want to insert number of cells based on a cell value.

I have sheet1, i want to use b4 as a reference as to the number of sheets (which is a template) to be inserted.

Example, if value of b4 = 4, I'd like to copy the template sheet 4 times.

How do i do that in vba?

THANKS. :)

Answer 1

Or something like this...

Sub CopyTemplate()
Dim ws As Worksheet, wsTemplate As Worksheet
Dim n As Integer, i As Long
Application.ScreenUpdating = False
Set ws = Sheets("Sheet1")
Set wsTemplate = Sheets("Template")     'Where Template is the name of Template Sheet, change it as required.
n = ws.Range("B4").Value
If n > 0 Then
    For i = 1 To n
        wsTemplate.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = i
    Next i
End If
Application.ScreenUpdating = True
End Sub

Answer 2

Something like this should work:

Sub copySheets()

Dim i As integer

Dim n As integer 'the amount of sheets

n = Cells(4, 2).Value 'b4

For i = 2 To n
    If ActiveWorkbooks.Worksheets.Count < n Then 'Makes sure the sheets exists
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    End If
    ws1.Copy ThisWorkbook.Sheets(Sheets.Count) 'copy data

Next i

End Sub

Answer 3

No magic, create them one by one in a loop, place each new one at the end. Edit: You want also to rename them 1, 2, 3, 4,.. so:

Sub CreateSheets()
    Dim i As Long
    With ThisWorkbook.Sheets
      For i = 1 To Sheet1.Range("B4").Value2
        .Item("Template").Copy After:=.Item(.Count)
        .Item(.Count).Name = i
      Next
    End With
End Sub