Copying VBA Modules Between Workbooks

This is a cross posting of a post I have made at another site. I suspect I may have challenged that group as there hasn't been much along the line of support. Perhaps there is no solution. I thought I'd post here to tap into the skill of a different group hoping to get some direction.

In my VBA project, a worksheet is created, copied to a new book and saved. This worksheet is like an internal user order form and has a few macro embedded shapes on it for use by the user using the form which is emailed to them.

The macros are stored in the workbook that created the initial worksheet. The users receiving the email with the form in it with the macro embedded shapes do not have access to the original creation workbook, so the macros embedded on the attachment they receive are not executable.

How can I send out that document with the macros available to the user? (I know this is can be an unsafe practice from the recipient standpoint, but no harm is intended with these macros.)

My email distribution code is below ...

Please Login or Register  to view this content.

On further research, I found that what I simply (really? simply??) need to do is import the module holding the macros for the buttons from the master workbook to the target. The code in the macros is not written with any public variables or direct references to specific source worksheets etc. (all references are to activesheet or activeworkbook in the macros).

Here is the code (thanks to ExcelTips)to copy the module (which doesn't seem to be working)

Please Login or Register  to view this content.

Questions ....

1) Can anyone provide a solution as to why the exporting of the module isn't happening? There are no errors, it just doesn't happen.
2) The line is giving me an error ("Method 'SaveAs' of object '_Workbook' failed.") . It's trying the save the workbook with the newly imported module as an .xlsm file (which I believe if you have macros has to be saved as). I'm assuming it's failing because there are no macros associated with that workbook since the import failed. If issue #1 is taken care of, will that eliminate this error? Or will I still have this error? What could I do then?

Thank you in advance for your consideration!

.
One thing that will assist in troubleshooting any macro is to comment out (or delete entirely) the line "On Error Resume Next".

I did not review the email code for accuracy but I am presuming if you got it from Ron deBruin's site it is good. He does have an
example of making a copy of the existing workbook and attaching it to the email for sending.

Hi! Thanks so much for stepping in to offer assistance!

Logit ... I commented out the line you pointed out in the CopyModule code. It stopped on this line with the error "Method 'VBProject' of object '_Workbook' failed. This is a new one for me.

Please Login or Register  to view this content.

Andy, your approach seems simple enough so I gave it a go. I moved the simple lines of code for the shapes into that worksheet's object code. BTW, here is the code for the macro emedded buttons on the worksheet being copied to a new book.

Please Login or Register  to view this content.

The CopyModule code is eliminated altogether from the emailing code.

However, I run into a problems even before this associated with saving the copied workbook initially. The process of copying the worksheet to a new book is initially created in this module. (This creation is not the one that is emailed. The one emailed is the same worksheet but has some button functionality disabled for the recipient). Refer to this code ...

Please Login or Register  to view this content.

This code breaks at the line in red when it tries to save. Since the new workbook has vb associuated to it (in the worksheet object) it will not allow it to save with that (.xlsx) extension.
If I change the extension to ".xlsm" in the line in blue ... I get "This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type." as in breaks at the same line as above in red. It doesn't appear as though I can save the newly created workbook with the vba in the sheet.

It would appear that I have managed to overcomne the file extension related hurdle after a decent Google search.

I added the argument " FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled " which seems to have done the trick.

Jenn68

Great ! You have it working for you. Good feeling .. huh ?

What was the solution to : with the error "Method 'VBProject' of object '_Workbook' failed.

Hi again folks ...
So I thought I had things good to go. My worksheet (with code attached to the sheet object for the two macro enabled shapes on it) was copied to a new workbook and saved successfully as a .xlsm file. It gets sent off to the recipient.
The recipient opens the file and enables macros as prompted. However, when they click on the macro enabled button, they get the message "Cannot run the macro ''SRF_WP_07Jan19-13Jan19.xlsm'!SRF_Group6_Click'. The macro may not be available in this workbook or all macros may be disabled."

The workbook name matches, the only worksheet in the workbook is "SRF" There are two macro enabled shapes available to the user upon receipt of the workbook.

Please Login or Register  to view this content.

I do not have the same issue with the "Sub Group9_Click" macro.

Any thoughts?

Hi Andy. Your suggestion has revealed that I get the same error on the original.
I should have checked there first as it was easy then to dsicover the problem

Thanks for the hint!