VBA: Error 1004: Select Method of Range class failed

[crowso]

I'm writing some code to copy some information from one sheet to another and I know that without a worksheet reference <acronym title="visual basic for applications">VBA</acronym> will be looking at the range on what it regards as the active sheet.

However it says that it failed.

The code I'm trying to get it:

If Range("A2") > 0 Then
Sheets("Flights").Range("Revenue").Select <------ this is the bit which is failing!!
Selection.Copy
Sheets("Rows Linked to Flights").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Range("A2").Value = "" Then Range("B2") = ""
End If



Any help would be greatly appreciated!

 

[Osvaldo Palmeiro]

Hi.
Try activate the sheet before selecting the range, like this:

[B]Sheets("Flights").Activate
Range("Revenue").Select [/B]

 

[crowso]

It worked, thanks!

 

[MARK858]

@crowso, if you don't use select and selection then you don't need to Activate the sheet and the code will run a bit faster.

    If Range("A2") > 0 Then
        Sheets("Flights").Range("Revenue").Copy
        Sheets("Rows Linked to Flights").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                                           :=False, Transpose:=False
        If Range("A2").Value = "" Then Range("B2") = ""
    End If