Copy/Paste Code Error

[bpgolferguy]

Hello! Could someone tell me how to fix this code so it doesn't show an error when I click the button it's tied to? Thanks!

Private Sub TeeTimeDisplay_Click()
Range("AH2").Resize(.Range("CC2").Value).Copy
Range("J2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Sub

 

[Norie]

There seems to be something missing, a worksheet reference in front of Range("CC2").Value.

Either that or there shouldn't be a . in front of it.

 

[JackDanIce]

Might be wrong be think you have a '.' where you shouldn't. Try:

Private Sub TeeTimeDisplay_Click()
Range("AH2").Resize(Range("CC2").Value).Copy
Range("J2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Sub

 

[bpgolferguy]

I tried just what you put there Jack and it prompted me to take out the End With....not sure why it was there anyway....so I did, but still get the debug error.

 

[Norie]

Perhaps you do need a With to go with the End With - one for a worksheet?

It could be that because there is no worksheet reference VBA is looking at the wrong worksheet for the value to Resize by.

 

[JackDanIce]

I just ran this code, not attached to a button, and it worked fine:

Sub KillerBees()
Range("A1").Resize(Range("D4")).Copy
Range("A4").PasteSpecial Paste:=xlPasteValues
End Sub

It's virtually identical to yours, so try:

Private Sub TeeTimeDisplay_Click()
Range("AH2").Resize(Range("CC2").Value).Copy
Range("J2").PasteSpecial Paste:=xlPasteValues
End Sub

If that doesn't work then I'm not sure...

 

[bpgolferguy]

Nope, it's not wanting to do it. The sheet name is Sheet1 if that makes a difference to add that...but i'm not sure how to do that....

 

[Norie]

If you want to add a sheet reference.

Private Sub TeeTimeDisplay_Click()
    With Worksheets("Sheet1")
        .Range("AH2").Resize(.Range("CC2").Value).Copy
        .Range("J2").PasteSpecial Paste:=xlPasteValues
    End With
End Sub

 

[JackDanIce]

No idea then, it's working fine for me with the code I tried. If you want to add Sheet1 then maybe:

Private Sub TeeTimeDisplay_Click()
With Sheets("Sheet1")
  .Range("AH2").Resize(.Range("CC2").Value).Copy
  .Range("J2").PasteSpecial Paste:=xlPasteValues
End With
End Sub

But if it previously wasn't working, don't see why above would now..

 

[bpgolferguy]

If you want to add a sheet reference.

Private Sub TeeTimeDisplay_Click()
    With Worksheets("Sheet1")
        .Range("AH2").Resize(.Range("CC2").Value).Copy
        .Range("J2").PasteSpecial Paste:=xlPasteValues
    End With
End Sub

That worked! Thank you!