Help with marcro; copy tabs based on list

[endlessboundless]

Hello,

Looking for some help regarding a macro, hope someone can please help.

So I have a list on a sheet named "input" which users can populate with department names (eg 1, 2, 3, ect). I also have a blank template called "template". I would like a macro that would duplicate the template tab and rename the tab based on the department names entered. So in the example above, "template" would be duplicated 3 times and the names of the tabs would be 1, 2 and 3.

TYIA,
Amit

 

[hockey1859]

Try this one. You might have to change a few things to match your specifics, but should do the trick.

Sub Copysheets()

'Makes sure you're on the right sheet
'change to match whatever sheet name has the list on it
Sheets("StartSheet").Select

'Sets active sheet as a variable for easy reference
StartSheet = ActiveSheet.Name

'Moves to the first cell in the list
'change to match the first cell in list range
Range("A1").Select

'Loops through the list, copying the template sheet to a new sheet at the end
'and renames it the value from the list
Do While ActiveCell.Value <> ""
SheetName = ActiveCell.Value
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = SheetName
Sheets(StartSheet).Select
ActiveCell.Offset(1, 0).Select
Loop

End Sub

HTH.

 

[endlessboundless]

Thanks you very much, worked like a charm.

If you could help me, I've posted another question regarding an existing macro that I have. Could you help with this aswell?