select method of range class failed

[SSPoulin23]

I cannot for the life of me get this to stop happening. I have a button on a summary page that, once clicked, unhides a sheet makes a copy, and would reference the information on the summary sheet onto the newly copied sheet. there will be other functions as well, but I cant get the "select method of range class failed" to go away! ive used this code before in another workbook but am having trouble here. any help would be great appreciated. thank you!

Private Sub CommandButton1_Click()

' Unhide Sheets
Sheets("Summary").Select
Sheets("Template").Visible = True

' Create Duplicate Sheets
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(1)
' ReassignCellRefs
Sheets("Template (2)").Select
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Summary'!R[-2]C"

End sub

 

[SSPoulin23]

I need them both thank you joe4!!!!

 

[Joe4]

Typically, command buttons are given different numbers, so it is rare that you would have two with the exact same name. I know you cannot have that on the same sheet, but I never really tried across different sheets to see if it allowed that.

Best practices would seem to dictate using different names to avoid confusion.

 

[SSPoulin23]

sorry for not being clear - they ARE on separate sheets.

 

[Joe4]

sorry for not being clear - they ARE on separate sheets.

Even so, I would still recommend using different names.

 

[SSPoulin23]

can I just change it to "commandbutton2_click ()"?

 

[Joe4]

You usually want to change it to match the actual name of the Command Button (so it makes sense).

I did some initial testing, and it looks like if you create button on different sheet it may re-use the same command button name.
So, if you do that, you may actually want the code on the Sheet module, to avoid confusion.

As you have seen, things can sometimes get a little flaky when working across different sheets from within the Sheet modules. If you do so, you may want to qualify all range references by prefix them with the sheet name, and see if that resolves the issues you were having.

Or, you can create code in a General Module to do what you want, and then just have your Command Button click code in the Sheet module call that procedure.

 

[SSPoulin23]

thank you for all of the clarifications Joe4!!!