Put a macro on a macro-created workbook

kweaver

Well-known Member
Joined
May 12, 2009
Messages
2,934
Office Version
  1. 365
  2. 2010
After a bunch of calculations and reformatting, etc., etc., I have a macro that saves a worksheet in a newly created workbook.
I use this code to accomplish the task:

Code:
Set zNewWB = Workbooks.Add      
ThisWorkbook.Sheets("Reformatted").Copy before:=zNewWB.Sheets(1)
zNewWB.SaveAs sSaveAsFilePath

What I want to do once this has been done is bury a macro in this new workbook that upon the user clicking a button will:

1. Delete the sheet named "Sheet1" and
2. On the sheet named "Reformatted" in the new workbook (called sSaveAsFilePath)
a. delete the first row and
b. delete columns E and F
3. Save the Workbook as a CSV file.

I have been messing around with this but can't get it done. Thanks in advance for an approach/solution.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Using VBA to alter the VBAProject of a workbook is due to security reasons not possible anymore. However, you could go back to ancient times ..... excel-4-macros
 
Upvote 0
Just put the code in the sheet module & it will be in the new workbook.
 
Upvote 0
Use a template with the code already in it.
 
Upvote 0
Thanks, Fluff, for the advice but I don't know how to do that from the saving macro or what to put in that macro -- I guess a 2-column delete and a 1-row delete, and resave as a CSV file.
But getting it there...I'm at a loss.
 
Upvote 0
I suspect that with all the security surrounding VBA I should just abandon this idea. Thanks anyway.
 
Upvote 0
Put the code in a 'template' workbook, save the worksheet to that workbook rather than a blank workbook - pretty straightforward and no security issues.
 
Upvote 0
Thanks, Fluff, for the advice but I don't know how to do that from the saving macro or what to put in that macro
You don't do it via a macro. Rather than putting the code in a regular module & trying to copy it to the new workbook, just put the code in the sheet module. That way when the sheet is copied the code goes with it.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top