Save workbook with Macros

[judgejustin]

I am trying to copy a workbook and save it as a new file. The problem, using the code below, is that it references the macros in the new workbook back to the original. I need the macros copied along with the workbook. Can someone tell me if I am missing something or just going about it completely wrong.

Sub Create_New_Workbook_Click()
 
    Set NewBook = Workbooks.Add
 
    ThisWorkbook.SaveAs
    ActiveWorkbook.Sheets("Sheet1").Visible = False
    ActiveWorkbook.Sheets("Void Control").Visible = False
    ActiveWorkbook.Sheets("Set-Up Voids").Visible = False
Dim myValue As Variant
myValue = InputBox("Enter Tax Roll Sequence # of the Parcel to be voided.", "Void Sheet Input", 0)
    ActiveSheet.Unprotect "password"
Range("F17").Value = myValue
ActiveSheet.Protect "password"
Dim sFile As String
Dim sPath As String
sFile = Range("F17").Value & ".xlsm"
sPath = "\" & sFile
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFile, FileFormat:=52

End Sub

 

[JLGWhiz]

that it references the macros in the new workbook back to the original

Could you give an example of what you mean by this statement. I have saved macro enabled files to different directories and the same directory without encountering a problem with the macros referring to anything other than what they were written to apply to. You can open the vb editor click on Tools; Macros to display the macros in your public modules and at the bottom there is a box that tells you which project (Workbook) they apply to. Does that box actually refer back to the original workbook if only the new workbook is open?

As for your code

sPath = "\" & sFile

is the only thing I noticed that doesn't do anything.

 

[judgejustin]

I actually meant to take that line of Code out that you stated doesn't do any thing. It can be disregarded.
When I run this macro the new workbook that is created has other buttons in it that are assigned to other macros. If you click on any of these buttons to run their associated macro it actually opens the original workbook first. When I check to see what macro is assigned to the button it is listed as one stored in the original workbook, not the new workbook where the button is located.
The macros themselves are being copied into the new workbook but the buttons that are linked are still assigned to the original workbook.

 

[My Aswer Is This]

You said:
The macros themselves are being copied into the new workbook but the buttons that are linked are still assigned to the original workbook.

Show me the macro script.

 

[judgejustin]

Not sure what you mean by that.

 

[My Aswer Is This]

I want to see the macro you say is not working the way you want.

Not sure what you mean by that.

 

[judgejustin]

This is the Macro that creates a the new workbook.

Sub Create_New_Workbook_Click()
 
    Set NewBook = Workbooks.Add
 
    ThisWorkbook.SaveAs
    ActiveWorkbook.Sheets("Sheet1").Visible = False
    ActiveWorkbook.Sheets("Void Control").Visible = False
    ActiveWorkbook.Sheets("Set-Up Voids").Visible = False
Dim myValue As Variant
myValue = InputBox("Enter Tax Roll Sequence # of the Parcel to be voided.", "Void Sheet Input", 0)
    ActiveSheet.Unprotect "password"
Range("F17").Value = myValue
ActiveSheet.Protect "password"
Dim sFile As String
Dim sPath As String
sFile = Range("F17").Value & ".xlsm"
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFile, FileFormat:=52

End Sub

There are several macros in the workbook that is created by the above listed one. Here is an example of one of them

Sub Add_Picture_Click()

Dim myFiles, e
    myFiles = Application.GetOpenFilename(, , , , True)
    If Not IsArray(myFiles) Then Exit Sub
    For Each e In myFiles
        With ActiveSheet
        .Protect "password", DrawingObjects:=False, Contents:=True, Scenarios:=True
            ActiveSheet.Shapes.AddPicture (e), False, True, Range("B5:B11").Left, Range("B5:E5").Top, Range("B5:E5").Width, Range("B5:B11").Height
            End With
    Next
End Sub

The button that controls it stays assigned the the macro stored in the original worksheet. If I email the new worksheet to someone else they will have the macro (which is locked) but the button will not work because it is assigned to the macro in the original worksheet which is on my computer.

 

[JLGWhiz]

This link might be what you need.

Break a link to an external reference in Excel - Excel

It is your buttons which are linked to the original worksheet, not the macros. You might need to re-assign the buttons once the links are broken. Haven't gone through the process myself, so don't know for sure.