Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 85
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Macro to create newsheets

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 85
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a macro to create excel line graph with coloured pointers anddata lables anuj datta Charts and Charting in Excel 1 September 30th 09 04:04 PM
Can someone create this macro for me Please? Aussiegirlone Excel Discussion (Misc queries) 1 July 25th 09 12:48 PM
What is a macro, how to create and use it? ADNAN TEEJA New Users to Excel 2 August 6th 08 10:22 PM
How to create a Macro.. Johnny Excel Discussion (Misc queries) 0 November 21st 07 07:47 PM
Using a macro to create a macro in another workbook Gizmo63 Excel Worksheet Functions 2 May 15th 06 09:48 AM


All times are GMT +1. The time now is 05:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"