Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create newsheets
Hi,
Im trying to use a macro to create a workbook of reports for 2010. I need a macro that will copy and paste from 3 master sheets to many new sheets in my workbook and name the sheet tabs accordingly. The first sheet is titled Sum, and needs to be copied and pasted 52 times, one for each week. The second sheet is titled ST, and needs to be copied and pasted 12 times, once for each month. The third sheet is titled CT, and needs to be copied and pasted 12 times, once for each month. So far I have a macro that will create the new sheet names. I will use a blank sheet to write the new sheet names i.e. ST Jan, CT Jan, ST Feb, CT Feb, etc€¦€¦ then select the range and run the macro. Sub CreateManyWorksheets() For Each cell In Selection ThisWS = cell.Value Worksheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = ThisWS Next cell End Sub Then I can copy and group paste, but if its possible, I would like to have macro complete the task. I was thinking there might be a way to have the macro recognizes the new sheet name to copy and paste from the appropriate master sheet. Is there some sort of For Each loop that could perform this. If not, then maybe 3 separate macros would work. Any help is appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create newsheets
Hi Dale
I would advise against creating so many sheets as part of your design. It is usually far easier, and better for subsequent analysis, to keep all data to one sheet (or perhaps 3 sheets in your case), with an additional column for Week number or Month Number. You can then use Filtering to select any Week or Month you require. If you want to mail me direct with a copy of your workbook, with some notes on what you are trying to achieve, I will try to show you what I mean. To mail direct roger at technology4u dot co dot uk Make the obvious replacement of at and dot to achieve a valid email address -- Regards Roger Govier "Dale G" wrote in message ... Hi, Im trying to use a macro to create a workbook of reports for 2010. I need a macro that will copy and paste from 3 master sheets to many new sheets in my workbook and name the sheet tabs accordingly. The first sheet is titled Sum, and needs to be copied and pasted 52 times, one for each week. The second sheet is titled ST, and needs to be copied and pasted 12 times, once for each month. The third sheet is titled CT, and needs to be copied and pasted 12 times, once for each month. So far I have a macro that will create the new sheet names. I will use a blank sheet to write the new sheet names i.e. ST Jan, CT Jan, ST Feb, CT Feb, etc€¦€¦ then select the range and run the macro. Sub CreateManyWorksheets() For Each cell In Selection ThisWS = cell.Value Worksheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = ThisWS Next cell End Sub Then I can copy and group paste, but if its possible, I would like to have macro complete the task. I was thinking there might be a way to have the macro recognizes the new sheet name to copy and paste from the appropriate master sheet. Is there some sort of For Each loop that could perform this. If not, then maybe 3 separate macros would work. Any help is appreciated. __________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to create newsheets
Thanks Roger,
I'll send it. "Roger Govier" wrote: Hi Dale I would advise against creating so many sheets as part of your design. It is usually far easier, and better for subsequent analysis, to keep all data to one sheet (or perhaps 3 sheets in your case), with an additional column for Week number or Month Number. You can then use Filtering to select any Week or Month you require. If you want to mail me direct with a copy of your workbook, with some notes on what you are trying to achieve, I will try to show you what I mean. To mail direct roger at technology4u dot co dot uk Make the obvious replacement of at and dot to achieve a valid email address -- Regards Roger Govier "Dale G" wrote in message ... Hi, Im trying to use a macro to create a workbook of reports for 2010. I need a macro that will copy and paste from 3 master sheets to many new sheets in my workbook and name the sheet tabs accordingly. The first sheet is titled Sum, and needs to be copied and pasted 52 times, one for each week. The second sheet is titled ST, and needs to be copied and pasted 12 times, once for each month. The third sheet is titled CT, and needs to be copied and pasted 12 times, once for each month. So far I have a macro that will create the new sheet names. I will use a blank sheet to write the new sheet names i.e. ST Jan, CT Jan, ST Feb, CT Feb, etc€¦€¦ then select the range and run the macro. Sub CreateManyWorksheets() For Each cell In Selection ThisWS = cell.Value Worksheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = ThisWS Next cell End Sub Then I can copy and group paste, but if its possible, I would like to have macro complete the task. I was thinking there might be a way to have the macro recognizes the new sheet name to copy and paste from the appropriate master sheet. Is there some sort of For Each loop that could perform this. If not, then maybe 3 separate macros would work. Any help is appreciated. __________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________ The message was checked by ESET Smart Security. http://www.eset.com . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a macro to create excel line graph with coloured pointers anddata lables | Charts and Charting in Excel | |||
Can someone create this macro for me Please? | Excel Discussion (Misc queries) | |||
What is a macro, how to create and use it? | New Users to Excel | |||
How to create a Macro.. | Excel Discussion (Misc queries) | |||
Using a macro to create a macro in another workbook | Excel Worksheet Functions |