Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to I create a macro in Excell to add multiple worksheets?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to I create a macro in Excell to add multiple worksheets?
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 -- Regards Ron de Bruin http://www.rondebruin.nl "TBarnes" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to I create a macro in Excell to add multiple worksheets?
Add the sheet name( sheet with the list of tasks ) to the code also
For Each cell In Sheets("sheet1").Columns("A").SpecialCells(xlCellT ypeConstants) -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin http://www.rondebruin.nl "TBarnes" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create multiple worksheets from list | Excel Discussion (Misc queries) | |||
How do I create an overall graph from Multiple worksheets? | Excel Worksheet Functions | |||
create & name multiple worksheets | Excel Worksheet Functions | |||
create multiple worksheets in workbook from one csv file | Excel Discussion (Misc queries) | |||
CREATE MACRO TO COPY MULTIPLE WORKSHEETS | Excel Discussion (Misc queries) |