create new sheets and rename it based on lists

[koi]

Hi All,

I have 2 sheets named "list" & "template", on "list" sheet A1 is New1, A2 is New2 etc until A25 is New25

all i want is the macro to copy the whole "template sheet" and then create new sheet but rename the new sheet as as per "list" sheet A1:A25

and if I want to only create 10 sheets for now so it will make New1 until New10...then later on if i want to create more it will check if the sheets name already exist

and start from New11 until New25 for example.

can it be done with vba?

Thanks All

 

[AlanSidman]

Option Explicit

Sub AddWorksheets()
    Dim I As Long
    Dim Last As Long
    Dim sname As String
    sname = "Sheet"
    Last = InputBox("How many sheets to add?")
    For I = 1 To Last
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = sname & I
    Next I
End Sub

 

[Fluff13]

Another option

Sub koi()
   Dim Cl As Range
  
   With Sheets("List")
      For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
            Sheets("Template").Copy , Sheets(Sheets.Count)
            ActiveSheet.Name = Cl.Value
         End If
      Next Cl
   End With
End Sub

 

[koi]

Thanks All for the help,

@Alan, the approach you gave me is giving me error and only add 1 sheet at the time
@Fluff13, perfect solution, i can always put new name on "List" sheet A1 to A something....then run the macro, and it will create the list accordingly, meaning i can always add 10 names at the time and run macro

Thanks again for All !

 

[Fluff13]

Glad we could help & thanks for the feedback.