Copy Module to new workbook & add button

[JBRTaylor]

Hi
I am trying to copy a module containing a macro from one spreadsheet (copymod.xls) to another workbook (book1.xls) and then insert a button to run the macro I have just copied into book1.

Using the code below the macro runs from the copymod sheet and not the book1 sheet as I would like it to. I am quite new to VBA and don’t fully understand all the code yet.

Any help would be greatly appreciated.
Thanks in advance

Sub copymod()
    Workbooks.Add
    Dim FName As String
With Workbooks("copymod")
    FName = "C:\code.bas"
    .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
Kill "C:\code.bas"
    ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5).Select
    Selection.OnAction = "msg"
    Application.CommandBars("Forms").Visible = False
    Range("F8").Select
End Sub

 

[JBRTaylor]

At the moment

wbkDest = active workbook

Before that bit of code i am using the following to save the file name

'save the file
Dim sFile As String
Dim uname
Dim path
Dim NewPathName As String
FileExt = ".xls"
NewFileNameA = Range("E9").Value
NewFileNameB = Range("G7").Value
NewFilePath = Range("M9").Value
NewPathName = NewFilePath & NewFileNameB & " Invoice - " & NewFileNameA & FileExt
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=NewPathName

Unsure if that could be used to set the correct path for the macro, or even if there is some better code to do the save as.

 

[JBRTaylor]

I have come up with what is probably quite a lazy work around
please excuse me for describing it and not using the code but i'm already confusing myself.
the code performs the following actions:
copy cells from master workbook to a new workbook and format them
save new work book as C:\temp.xls
(Now i can use the C:\temp.xls to copy the macro across to and as an address for the macro button, or anything else)
copy macro across from master workbook to temp.xls.
rename temp.xls using the code in my last post to save it with a name and file path from within the worksheet
kill the temp file C:\temp.xls