Copying a button from one sheet to automatically run code when copied to new sheet.

[Ronanm]

Hi I am struggling with getting a macro to run when I create a new button on a sheet. I assign the On Action to the button and the macro runs without debug, but... the OnAction doesn't run the Macro "Test".

The Button being copied to Sheet2 is on Sheet1, then the Macro should automatically run when the button is pasted to Sheet2.

Any ideas?

Sub Macro1()

    Sheets("Sheet1").Visible = xlSheetVisible
    Sheets("Sheet1").Select
On Error Resume Next
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
  
    Selection.Copy
    
    Sheets("Sheet2").Select
    With ActiveSheet.Buttons.Add(500.5, 5.75, 90, 46.5)
        .Name = "New Button"
        .Size = 11
        .Bold = True
        .Characters.Text = "Run second Macro"
        .OnAction = "Test"
    End With
    ActiveSheet.Shapes.Range(Array("New Button")).Select
    
Sheets("Sheet1").Visible = xlSheetHidden
End Sub


Sub test()
    Sheets("Sheet2").Activate
    Cells(1, 1).Select
    MsgBox ("Macro finished, and in Cell A1")
End Sub

Thank you.

 

[DanteAmor]

If you are going to copy and paste the button, try this

Sub Macro1()


    Sheets("Sheet1").Visible = xlSheetVisible
    Sheets("Sheet1").Select
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
  
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    
    With Selection
        .Name = "New Button"
        .OnAction = "Test"
    End With
    
    Application.Run ActiveSheet.Shapes("New Button").OnAction
    
    Sheets("Sheet1").Visible = xlSheetHidden
End Sub

 

[Ronanm]

Many thanks DanteAmor, that does the job nicely

Apprciate it.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.