Macro to create newsheets

[Dale G]

Hi,
I’m 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 it’s 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.

 

[Don Guillett]

Instead I suggest ONE sheet using autofilter for the days and months, etc.
Much easier.

 

[Roger Govier]

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

Hi,
I’m 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 it’s 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

 

[Dale G]

Thanks Roger,

I'll send it.

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



__________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com



.