How do I get my controls to copy with my worksheet?

[krice1974]

Hey all. I've written a method to copy a worksheet that contains a couple of command buttons at the top of it. The sheet is copied fine, but the command buttons and a text box at the top of the sheet won't copy. I experimented with adding a dummy sheet and button, and recording copying them. I see that the add button is grabbed in the macro, but not when I record copying my sheet of interest. Any ideas? Here's the code:

Sub NewAccountManagerSheet(SheetName As String, AccountManager As String, Area As String)
    
    Dim shName As String
    
    shName = SheetName & "-MA" ' "for manager area."
    
    Sheets("AccountManagerTemplate").Unprotect
    Sheets("AccountManagerTemplate").Copy Before:=Sheets(2)
    Sheets("AccountManagerTemplate (2)").Name = shName
    Sheets(shName).Range("AccountManager") = AccountManager
    Sheets(shName).Range("Area") = Area
    Sheets(shName).Activate
    Sheets(shName).Protect DrawingObjects:=False, Contents:=True, Scenarios:=False
    
End Sub

 

[Andrew Poulsom]

Are you sure that the objects aren't being copied? This worked for me:

ActiveSheet.Copy Before:=Sheets(2)

 

[krice1974]

I wouldn't lie to ya. The code above doesn't copy the buttons on the sheet "AccountManagerTemplate."

 

[Andrew Poulsom]

Well I've never known that to happen. In fact I just tested your code as below and it worked as expected (Excel 2010):

Sub Test()
    Call NewAccountManagerSheet("NewSheet", "Andrew", "Derby")
End Sub

Are you sure that your code is referencing the correct workbook?

 

[krice1974]

Well I've never known that to happen. In fact I just tested your code as below and it worked as expected (Excel 2010):

Sub Test()
    Call NewAccountManagerSheet("NewSheet", "Andrew", "Derby")
End Sub

Are you sure that your code is referencing the correct workbook?

It is. When I copy the sheet with click and drag, it isn't copying them then, either.

 

[krice1974]

Thanks for the help, Andrew. Excel just crashed, and upon restarting, it is now copying the buttons properly. All is well that ends well..

 

[RoryA]

In Excel Options in the Advanced section is a setting to Cut, Copy and Sort inserted objects with their parent cells. If that's unchecked, then they don't get copied with the sheet.

 

[krice1974]

Great Rory. I'm checking that out now. Thx..

 

[deb_o]

This worked for me. Thanks Rory!