VBA saving tab in new workbook but button linked to old

cogswel__cogs

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top