How to I create a macro in Excell to add multiple worksheets?

[Guest]

I have a workbook that different departments partake in. There is an info
sheet that contains a list of tasks and a worksheet is created for each
department for each task and then there is a summary worksheet to combine
each of the sheets. Is there a way (macro) to "automatically" create/copy
the required sheets, per task, for each department?
What I have been doing is just copying the sheet that I create for the first
dept and renaming it so that it is easy for each department to just go to
their sheet(s) within the workbook. However, this can be very time consuming
as some projects may have 50 or more tasks and a worksheet needs created for
each task and each department. So if there are 3 depts and 50 tasks, then
150 sheets need to be created/copied.

 

[Ron de Bruin]

Hi

With the list of tasks in column A
This will create a copy of the sheet template and give it the name of the cell
Be sure that there are no duplicate names in the list

Sub test()
Dim cell As Range
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
Sheets("template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
Next
End Sub

 

[Ron de Bruin]

Add the sheet name( sheet with the list of tasks ) to the code also

For Each cell In Sheets("sheet1").Columns("A").SpecialCells(xlCellTypeConstants)