Inserting a new Worksheet Using Macros

Birdy92

New Member
Joined
Apr 15, 2002
Messages
14
What I have is a worksheet where its name is never the same. IE, the original sheet is named A.

What I know how to do is make sheet A copy itself and make sheet B (I know it would be A(2) but I can rename it to B).

Now, using the same macro, from sheet B I am looking for sheet B to copy itself and make sheet C (or B(2) that can be renamed to sheet C).

I hope this is not clear as mud, any help you can give would be very helpful.

Bill
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Birdy92

New Member
Joined
Apr 15, 2002
Messages
14
What I have now is in this form:

Sheets("2003 Sales").Select
Sheets("2003 Sales").Copy After:=Sheet(1)

What I am doing is renaming Sheet(1) to January Sales

What I would like, if it can be done, is at the end of Feburary run a macro that would create a Feburary Sales sheet(from the previous month sheet, in this case Jan), and do the same for the rest of the year, but using the same exact marco to create every new sheet.

The reason for running the macro is that all of the sales figures are updated via a pivot table and not inputed by hand, and I am also trying to keep a running tally for the year, but shows the YTD info for that period (ie. it copies/paste values Grand Total (2003+Jan) onto another line and adds Feb to to give a new Grand Total...March would be Grand Total (2003+Jan+Feb) to a new line plus March Sales for a new Grand total, so on and so forth).

Now, I hope that I haven't made too much mud ;)

Bill
 

Birdy92

New Member
Joined
Apr 15, 2002
Messages
14
Looking thru the boards I found something that is very close to what I am looking to do. What was posted by TommyGun was this:

Sub CopyValues()
ActiveSheet.Copy
ActiveSheet.UsedRange = Active Sheet.UsedRange.Value
End Sub

The one thing that I want to do is instead of this going into a new book I want it to stay in the same book that I am working in as a new sheet.

Please tell me there is a way to make this happen

Bill
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top