VBA save macro enabled file refere to original file

Question

I looked here and tried many solutions but could not get my code run as expected.

I have one macro enabled workbook with one module on a sheet named "Original"

I created have a second sheet as master where a add a button to copy the the "original" sheet and save the copy as macro enabled.

When I open the copied file the macro still refer to the initial file. I want the macro be just in the copied file because I cannot distribute the source file to the users.

Following is my code

==>How i copy the file

 Sub createNew(fineName As String)
    Dim mybook As Workbook
    Set mybook = ThisWorkbook
    Set newBook = Workbooks.Add
    mybook.Sheets("Original").Copy Before:=newBook.Sheets(1)
    Set newWs = newBook.Sheets("Original")
    newWs.Name = Left(fineName, 30)  
End Sub

==>How I save the file

 Sub savefile(fname As String, compid As Long)
    fname = "PS_" & fname
    Set newBook = ActiveWorkbook
    Application.DisplayAlerts = False

        newBook.SaveAs fname, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.DisplayAlerts = True

   closefile newBook

End Sub

I will appreciate your help!

Maybe [this article](https://stackoverflow.com/questions/18497527/copy-vba-code-from-a-sheet-in-one-workbook-to-another) will help you, explain how to export the macro module to another book. — Fernando Madriaga, Jul 10 '17 at 13:15
*"When I open the copied file the macro still refer to the initial file"*: Actually no code is saved in the new workbook except that of the copied worksheet. Macros in other modules of your source workbook are not copied at all into the new workbook. And they **do** refer to their own wotkbook. I don't understand exactly the problem you are facing. Can you clarify further? — A.S.H, Jul 10 '17 at 13:40
Thank you very much. I want the newly copied file to have the macro module too. — Adjeiinfo, Jul 10 '17 at 13:48
@Fernando. Thank you. I copied the sheet but the code is not inside — Adjeiinfo, Jul 11 '17 at 00:26

score 1 · Accepted Answer · answered Jul 11 '17 at 04:19

Thank you all. I could solve the problem. Thanks to your comments I realized I had I had to copy the module to the newly copied file

Sub CopyOneModule()
Dim FName As String
With Workbooks("Book2")
*** FName = .Path & "\code.txt"
*** .VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
End Sub

VBA save macro enabled file refere to original file

1 Answers1