VBA saving tab in new workbook but button linked to old

cogswel__cogs

Board Regular
Joined
Jan 3, 2018
Messages
98
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,954
You have to change the button's OnAction property in the new workbook to include the full name of the new workbook. For example:
Code:
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
 

Forum statistics

Threads
1,078,352
Messages
5,339,715
Members
399,320
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top