email a vba code-containing sheet

[HarryKlein]

Hi All,

I have a workbook, that has a sheet that contains some code.
I want to email this sheet, and I want the receipient to execute the macro, therefore I copy this sheet into a new workbook and email it.
When the user opens th workbook, he cannot execute it, as the macro still refers the source-file, which is logic, but I didn't think about it.
What is the way of sending a sheet only with some code? (I don't want to send the entire workbook.)
Thanks

[Keebellah]

The same way, but keep in mind that if your code is in the VBA project itself you will need to send that too, so simply said.
Email the workbook with the code and remove the unnecessary sheets

[HarryKlein]

Thanks for your answer.
The information I was looking for - maybe my question was not accurate enough - how can I update the reference of the macro, when I copy it from its original place and put into a different workbook.
And the answer (or one of the possible answers is) is that the shape, that runs the macro has an property that called OnAction, and it can be changed programatically.
So, if you move your sheet that has a macro, the OnAction-parameters should also be updated accordingly.

[Keebellah]

Can't help without the actual file since I don't know what you're doing when you send the email.
I suggest you make sure it does not contain private data and upload a sample file with the situation you have and what you want.

[LJMetzger]

Hi Harry,

can I update the reference of the macro, when I copy it from its original place and put into a different workbook.

a. When you save a file in a manner such as 'save as' the 'OnAction' references all point to the 'save as' Workbook. If you delete things from the 'save as' Workbook everything will point to the proper place.

b. When you copy sheets, the 'OnAction' references point to original File . You have to manually or programmatically change the 'OnAction' references to point to the correct workbook.

Lewis