Insert a macro in an about-to-be-created workbook

kweaver

Well-known Member
Joined
May 12, 2009
Messages
2,934
Office Version
  1. 365
  2. 2010
I have a macro that spins off a specific sheet from a workbook and saves it in a given path.
Before saving it, I'd like to have a macro placed in this about-to-be-saved workbook.
I have the relatively short code for the macro to be place in it, just don't know what I should do to go about this association with the new workbook.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I realized just now that this new workbook would have to be saved as an xlsm as well.
 
Upvote 0
I'm using this to save the sheet (as an xlsx file)

NewWB.SaveAs sSaveAsFilePath

but need to save it now as an xlsm file (after I learn how to automatically create a module and insert my macro).
More complicated than I thought or was hoping.
 
Upvote 0
.
VBA Code:
Option Explicit

Sub SveXLSM()

Dim Path As String
Dim Filename As String

Path = "C:\Users\gagli\Desktop\"
Filename = "Test Save XLSM"

ThisWorkbook.SaveAs Filename:=Path & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

End Sub
 
Upvote 0
A quick search for "vba extensibility" will get you along nicely. It's not as difficult as you are thinking though your users' anti-virus software may cause you grief.
 
Upvote 0
OY. I just did a search as you suggested and browsed site of the late CPearson...phew...I think it IS a bit complicated.
I'll read through that tomorrow, but if anyone wants to shed light on this process, it'll be MORE than welcome.
 
Upvote 0
Maybe there is another angle. What is the bigger picture of what you are attempting?
 
Upvote 0
I currently have a workbook with input data that gets reformatted via a macro. Then, the resulting reformatted worksheet gets saved currently as an XLSX file.
However, the user now wants a macro in that resulting workbook that will enable him to select a cell in a row and the macro will copy that row and insert the copy below the existing row.
The macro to do that is simple. But, the issue is (a) how to get that macro in the resultant workbook, (b) create a module in the resultant workbook, (c) save the copy/insert macro into that module and, finally, (d) save that workbook, of course, as an XLSM file because of the macro existing in that workbook. I guess the icing on the cake would be to have a button on the resulting file and (only) sheet enabling the user to click on it after selecting the row to copy/insert and having it do its thing.
 
Upvote 0
How about copying the worksheet, code, buttons, and all to a new workbook and then saving. You could have a 'template' worksheet in your source workbook containing the code that will be used by the end user. Would that work?
 
Upvote 0
I'll have to think about how to do that. My first uncertainty is how to save the sheet as a macro-enabled workbook with the macro in a module of that workbook.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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