range.select error??

[aceee]

hey all

I have this:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub

it errors to: SELECT METHOD OR RANGE CLASS FAILED

any idea?

 

[RoryA]

If your code is in a worksheet module (which I suspect it is), then Range("A1") defaults to a reference to the sheet containing the code, which you can't select because it is no longer active. You need to qualify the range with the sheet:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub

or even:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1,0).PasteSpecial
End Sub

 

[aceee]

cheers

 

[aceee]

Worksheets("Sheet1").Range("A5:K299").Select
select method from class range failed???

 

[jindon]

You need to select parent object first.

With Sheets("sheet1")
    .Select
    .Range("a5:k299").Select
End With

BTW why are you selecting the range ?

 

[aceee]

thanks..
selecting a range, to copy and paste special into another sheet

 

[aceee]

don't understand why sometimes these things work.. and sometimes not.. now i get another error

With Sheets("sheet1")
    .Select
    .Range("a5:k299").Select
End With
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

range of method failed on the pastspecial command...

 

[jindon]

No need to "Select/Activate"

Sheets("sheet1").Range("a5:k299").Copy
Sheets("Sheet2").Range("a1").PasteSpecial xlPasteValues

 

[aceee]

but didn't u say i needed that "with" statement b4?

 

[jindon]

but didn't u say i needed that "with" statement b4?

If you still want to "Select", just do it in that way.
Your code doesn't copy anything, that's why error.