excelvbabuttonduplicates

Duplicate sheets with buttons

I currently have a single worksheet with 3 buttons that all run a subroutine if clicked. I have written another subroutine that copies the current sheet and pastes all the data into a new worksheet. The code is as follow:

Public Sub DuplicateSheet()
      ActiveSheet.Copy After:=Worksheets(Sheets.Count)
      On Error Resume Next
      ActiveSheet.Name = "Test Sheet"
End Sub

However, the new sheet only contains the data but not the buttons. How do I duplicate the buttons with the subroutines still assigned?

Thank you.

Solution

  • Excel Options >> Advanced section >> Cut, Copy and Sort inserted objects with their parent cells

    or in VBA set

    Application.CopyObjectsWithCells = True