Code to Copy a Module from on Workbook to another not working?

Hi,

I googled how to do this and came up with the following code. There are two subs, the first generates a "baby" report from a master document. At the end of the routine it is meant to call a sub to export a module of code from the source workbook to the newly created "baby" report.

I don't get any VBA errors but the module i want to export (module8) just not appear in the new workbook.

Any ideas? I'm not great in VBA!

I am running Excel 2010 in compatibility mode as the end users all have Excel 2003.

Thanks

J

Please Login or Register  to view this content.

And the below routine is meant to export the required module...

[/CODE] Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another


Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Export strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0


End Sub[/CODE]

Thank you on both counts,

The Sub CopyModule i found on another forum and i did not notice the the On Error Resume Next line...

Removing that gives the error:Programmatic Access to Visual Basic Object Is Not Trusted

However, Your idea for the template has a lot of merit as well! I may well look at doing that....

Thanks - i'll have a look in there anyway just to check my general security settings, tbf that has definitely pushed me towards your template idea as this report is going to be run on an Admin's machine with little excel ability and i am betting that IT have got all their security settings on high...

So, i've created the template on a shared drive and got the macro to look at that...hopefully will be a good workaround! Cheers