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

 

[Joe4]

Is cell C11 hidden, locked and protected, or part of a merged cell?

 

[Norie]

Does this work?

Sheets("Template (2)").Range("C11").FormulaR1C1 = "='Summary'!R[-2]C"

 

[SSPoulin23]

Joe4 it is merged; I tried range("C11:J11") but its not working either. is merged a problem?

 

[SSPoulin23]

Norie - yes that worked! thank you!! can't believe im so dense but thank you!!

 

[Joe4]

is merged a problem

Yes, yes, YES!

Merged cells are the devil and should be avoided at all costs! They wreak havoc with things like sorting and VBA.
You can get the exact same visual effect without all the issues by using Center Across Selection instead of merging the cells.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells | Excel training, testing and consulting by Tom Urtis, Microsoft MVP

Even if you have found a suitable workaround for this particular issue, I still strongly advise you to make the change, as there is a good chance they will cause you more heartache down the road.

 

[SSPoulin23]

consider it done Joe4!! and thank you for the advice on how to accomplish the same look!!

 

[Joe4]

You are welcome!

 

[SSPoulin23]

hey wait! I'm still having the same issue anytime I try to use Range.("J6").Select why is that? I'm still fairly novice with VBA, so many times I'll click the "record macro" button, do what I need to do, and then steal the code it kicks out and apply it to my scenario. It's using Range.("J6").Select but when I try to use it, I get the error message again. could it be because I'm using a private sub within a command button click? THANK YOU!!!

 

[Joe4]

I don't if you just made a typo, but you should not have a period after the word "Range".
It should be

[COLOR=#333333]Range("J6").Select

not
[/COLOR]

[COLOR=#333333]Range.("J6").Select [/COLOR]

By the way, when you record VBA code, you end up with a lot of Select, Activate, and Selection statements. This is because the Macro Recorder is very literal. You usually don'y need to actually select the cell to work with it. Most parts of code where one line ends in "Select" and the next begins with with "Selection" can be combined into one line, like Norie demonstrated for you above.