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]

ugh - yes, sorry. Typo. it Range("J6").Select Still causes the error.

 

[Joe4]

Can you select it manually?
Is there anything special about that cell (merged, locked, hidden, etc)?
What exactly are you trying to do to this cell?
What does the VBA code around this look like?

 

[SSPoulin23]

Sheets("Template (2)").Range("H23").FormulaR1C1 = "='Summary'!R[-2]C"
'Change Payment number
Sheets("Template (2)").Range("H6").FormulaR1C1 = "=RC[1]+1+1"
Range("J6").Select
Selection.Copy
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Template (2)").Select


my vba unhides a sheet, copies and pastes it before the first sheet, then references the information in a previous sheet for a cumulative effect. it's an internal invoice and this line of coding was to automatically number the next invoice with this next number. I know it wasn't there with it yet. then it was going to rename the new sheet with that payment number. if you have any other ideas I'd be totally happy to hear them!!!

 

[Joe4]

So these two lines:

[B]Range("J6").Select
Selection.Copy[/B]

can be combined to this:

[B]Range("J6").Copy[/B]

However, I am curious as to which sheet you are on when this line runs. The previous lines of code have sheet references along with the range references where this line of code doesn't.
If you add the following line above the Range("J6").Copy line, what does it return?

MsgBox ActiveSheet.Name

 

[SSPoulin23]

sorry for delay - wouldn't let me sign in yesterday. anyway, I'm writing my code on Sheet1(Summary) because that's where the button is - all of this is happening once the button is clicked. is that why it's getting fussy about the range("J6") ?

 

[Joe4]

I'm writing my code on Sheet1(Summary)

Are you saying that you are writing the code in the Sheet1(Summary) module in the VB Editor?
Yes, I have seen that cause complications. You should only put Event Procedure code in those modules.
All other VBA code (Command Buttons included) should really be in a General Module.

 

[SSPoulin23]

so if I put multiple buttons with the "Sub commandbutton1_click()" it's going to run an error right? do I rename one of them?

 

[Joe4]

You cannot have multiple procedures or function in the same module with the same name.
If you have the button running code from a different location (or running a different procedure), you will need to re-assign the button the the new code (or new code location).

 

[SSPoulin23]

oh so I can just add another module and they both keep their "commnadbutton1_click()" name? sorry to be so dense. I am still learning all of the "seven deadly sins of VBA" - thou shall not use merge and center ....

 

[Joe4]

oh so I can just add another module and they both keep their "commnadbutton1_click()" name?

Sure. However, if one is meant to replace the other, than I would get rid of the old one.
Otherwise, that can lead to confusion (i.e. if you edit the "wrong" code and wonder why no changes seem to be happening when it is run).