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

[kweaver]

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.

 

[Legacy 456155]

Is there any reason that you cannot run the code from within the worksheet? There are only a few exceptions that I can think of where you MUST run code from a standard module. The target workbook only has a single worksheet. Right?

 

[kweaver]

Right. I was (unfortunately) dreaming about it last night and could put the macro in the reformatted sheet before it's saved.
Is there something special I need to do to save it as an XLSM workbook rather than an XLSX?

 

[Legacy 456155]

Did you see Logit's earlier reply?
Record a macro of copying your worksheet into a new workbook. Save it as xlsm. View the recorded code and edit to suit.

 

[kweaver]

I have the code written that needs to be in the reformatted sheet, but I don't know how to save that sheet as a separate XLSM file. Currently, I save it as an XLSX file using:

zNewWB.SaveAs sSaveAsFilePath

And "sSaveAsFilePath" has an extension in it that's "XLSX"...I tried to adjust it to "XLSM" but the saving failed.

 

[kweaver]

OK. I implemented Logit's suggest and that worked perfectly to save the new sheet as a workbook XLSM.
My remaining question is how, from the original macro code, do I put this new macro in the only sheet of the new workbook?

Phew! Thanks for remaining on top of this for me.

EDIT: Never mind...I got it! YAY!!!


Kevin

 

[kweaver]

Spoke too soon...All's good with one exception. I need this new macro to run with a shortcut key.
Not sure where that code should go. I think the code should be: Application.Onkey "+^{C}",InsertCopyRow

 

[kweaver]

I almost have it. However, when I put the new macro on the sheet that's being saved (it's called "TimeSheet"), the sheet is actually renamed before saving to contain a time stamp.
It seems that the Workbook_Open (with the shortcut key) and the macro that's supposed to do the copy and insert are both missing in the newly named workbook when saved.
Actually, upon further examination, they're not missing...they're on the sheet. However, the Workbook_Open event doesn't seem to be firing when I open the file, so the shortcut isn't working.

I'm getting a message that the macro isn't there (the macro being called by the Workbook_Open event).

 

[kweaver]

I believe I found the problem but don't know how to fix it. The Workbook_Open event needs to be in "This Workbook" but I don't know how to get it into that workbook before the book is saved.

 

[kweaver]

Pulling my hair out. I put Auto Open() in the sheet that becomes the workbook.
The macro to copy and insert was placed in the TimeSheet. However, it seems that when I placed it there, it got date-stamped.
So, even though the Auto Open sets the shortcut, it's using the old date-stamp for the macro so it can't find it.
OY.

 

[Legacy 456155]

Unfortunately, Auto Open() is one of the exceptions that require a standard module.
With new factors in play, another option is to have a destination workbook that contains the code before-hand. You will copy the formatted worksheet to this destination workbook, and save a copy of the destination workbook. This can be done automatically with code as long as you know where the destination workbook is located. Another option is to create an add-in. Both are better than using the extensibility library IMO. Please post all of your relevant code and requirements..
I'll throw together an example that you can work with. Don't pull out your hair or you'll look like me.