Microsoft Excel + macro help

[BenJ]

Hi guys,

I need to write/record a macro which basically copies a worksheet and saves it as a new worksheet. But each time the Macro is clicked it needs to save the new sheet with a new name. Like 1 then 2, then 3 e.t.c

Anybody able to point me in the right direction???

Cheers

 

[Avathar77]

I can have a go but don't realy understand what you mean by "worksheet"
"WorkSheet" is a new work space in a Excel "workbook" - which has "Sheet1", "Sheet2", "Sheet3" by default in tabs at the bottom of the workbook.
If you want to rename the "Sheet1" or "Sheet2"(WorkSheet name) to a custom name it is easy to do in a macro.
But please clarify what you meant by worksheet and I will see if I can be of any help.

 

[BenJ]

by worksheet i mean exactly what you said...but instead of the macro making the next sheet in the series "Sheet1" "Sheet2" I want it to say "Order1", "Order2" e.t.c

So data in worksheet "Data" then when I click a macro it copies worksheet "Data" into "Order1", then next time I click the macro it copies "Data" into "Order2".

Are you following mate? Cheers for any help btw, much appreciated!

 

[Avathar77]

Seems simple enough! i will give it a go but bit busy in the week end

 

[BenJ]

Cheers buddy

 

[Avathar77]

ok seems easier than I thought

Just assign this macro to a button on the "Data" worksheet.
============================

Sub Macro2()
Dim intWorksheetCount As Integer
intWorksheetCount = ThisWorkbook.Worksheets.Count

Sheets("Data").Select
Sheets("Data").Copy After:=Sheets(intWorksheetCount)

Sheets("Data (2)").Select
Sheets("Data (2)").Name = "Order" & intWorksheetCount

ActiveSheet.Shapes("Button 1").Select
Selection.Delete
Sheets("Data").Select

End Sub
=================================

Or send me your email add via trust and I will send you the work book I tested this on.
Let me know how it goes.

 

[Avathar77]

Just sent you the file to your email.

 

[BenJ]

Thanks! All I need it to do now is delete columns A to D in the newly copied sheet "Order1" e.t.c

You couldn't tell me what to put for that could you??

Cheers

 

[BenJ]

Oh actually before it copies the sheet "Data" I need it to refresh the pivot table in the sheet, is that possible?

 

[Avathar77]

Any thing is possible just not 2day, i am at a party so i will hav 2 look at this tomorrow.

 

[BenJ]

Thanks

 

[Avathar77]

Right, couple of questions
1.Do you want the columns A to D "removed" or just the data in the cells of the Columns A-D deleted on the order sheets?
2.What table or data range are you using for the pivot table.
3.Do you want the pivot table to be in the order sheets as well?

 

[BenJ]

Ahh been away all week. Right I want the columns deleted completely. The data range will be columns a-c in a sheet called "Menu". Yes the pivot table will need to be in the order sheets please.

Cheers

 

[Avathar77]

Ok I think I have done it, but couldn't understand what is the sheet "Menu" is. If it is the first sheet you have to replace "Menu" instead of "Data" in both macro and worksheet.

I can't test the pivot table as I dont have any valid data range, so you have to test it with some of your data. I will send it now.

 

[BenJ]

Think it's getting a little bit confusing now so i've sent you a new workbook by mail!

 

[Avathar77]

I have just glanced over the description and I think it can be done by a simple way, but bit busy now so will have a look soon.

 

[Avathar77]

OK so this is happening as you explained because when copying a pivot table to another worksheet it copies with data, values and formatting options as well, you want only the values to appear in the Order# sheets.
This can be overcome by using paste special instead of copying a whole worksheet, and then formatting the values to a table.
It can be done but it will not be the same as you have i will send you the attachment later.