VBA saving tab in new workbook but button linked to old

[cogswel__cogs]

I have a report that allows users to make individual files for their reps.

However there are a couple of macro assigned buttons on the tabs that are used to make new files.

The code teavels with the transfer because I have it in the sheet area and not in a module.
However the button assignments still link back to the original as I am copying the sheet when making each new one..

Is there some way to have them move to the new file as well. Am I better just having the buttons be there on template sheet and assigning macros after the new workbook is created, can I do that?

 

[John_w]

You have to change the button's OnAction property in the new workbook to include the full name of the new workbook. For example:

Public Sub Save_Sheet()

    Dim clickedButton As Button
    Dim p As Long, newWorkbookFullName As String
    Dim newWorkbook As Workbook
    
    Set clickedButton = ActiveSheet.Buttons(Application.Caller)
    
    p = InStrRev(ThisWorkbook.FullName, ".")
    newWorkbookFullName = Left(ThisWorkbook.FullName, p - 1) & " COPY" & Mid(ThisWorkbook.FullName, p)
    
    ActiveSheet.Copy
    Set newWorkbook = ActiveWorkbook
    
    With newWorkbook.ActiveSheet.Buttons(clickedButton.Name)
        p = InStr(.OnAction, "!")
        .OnAction = "'" & newWorkbookFullName & "'" & Mid(.OnAction, p)
    End With
    
    Application.DisplayAlerts = False  'suppress warning if new workbook already exists
    newWorkbook.SaveAs Filename:=newWorkbookFullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
    newWorkbook.Close False

End Sub

 

[cogswel__cogs]

Thanks I will try