How to save Function code in an exported workbook ?

[Sparkle99]

I have a Workbook that generates a new worksheet that gets saved into a separate file for emailing to customers to fill-in and return.

The Workbook has functions that are used in formulas on the new sheet. So, when the new sheet is copied and saved to a new file, the function code is missing.
The Functions are in Module1.

How do I get the code into the new workbook ?
As the code is in functions, I can't just put the code onto the worksheet itself.
So, are functions the right way to do this ?

The code needs to be in place in the new Workbook or the naiive user will get errors.
Does anyone have any suggestions ?

 

[VoG]

This should help you http://www.cpearson.com/excel/VBE.aspx

 

[Sparkle99]

Many thanks Peter.
The code looks... interesting !
I think I get what is happening and have copied the code and made the various settings changes required.

Can I just clarify the use of "FromVBProject" and "ToVBProject"
Should these be the filenames of the 'Main XLS' and the 'Exported XLS' - the latter being where i wish to copy the Module of code ?

 

[VoG]

It would be like this

retval = CopyModule("Functions", ThisWorkbook.VBProject, ActiveWorkbook.VBProject, True)

 

[Sparkle99]

Many thanks Peter - it works a treat.

Forum question...
Is it possible to 'close' these threads ?
My question has been answered - the thread can be found by searching.

 

[VoG]

No, there isn't a mechanism for marking threads as resolved.