Select method of Range class failed | Selecting a range

[rachel06]

Hi,

I feel pretty silly for not being about to figure this one out. I'm literally just trying to do an hlookup in A1 and drag the formula all the way to the right. I'm getting an error on the last part of this code, no matter what i try.

What I've been trying to accomplish is entering the formula in A1, copying that, then offsetting that selection down a row and all the way to the right. After that, I'd go up one cell, select all the way to the left and paste the formula. That sounds super complicated now that I've typed it out but it should be pretty easy. No matter what I try, the bottom line errors with:

"Run-time error 1004: Select method of Range class failed.

Help appreciated!!

Sheets("1537").Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("1537").Rows("1:1").NumberFormat = "General"
Sheets("1537").Range("A1").FormulaR1C1 = "=HLOOKUP(R[1]C,Headers!R,1,0)"
Sheets("1537").Range("A1").Copy
Sheets("1537").Range("B2").Select

 

[MARK858]

Is Sheets("1537") the active sheet? you can only select a cell on the active sheet.
I can't see the rest of the code but I assume you are doing a paste, you don't need to select a cell to do a paste.

 

[rachel06]

Is Sheets("1537") the active sheet? you can only select a cell on the active sheet.

Ah, that would be it. It's not the active sheet.

I'll just add a piece to select that sheet. Thanks so much!!

 

[MARK858]

I'll just add a piece to select that sheet.

You don't need to select/activate the sheet. Post the following line so I can see what it is

 

[rachel06]

You don't need to select/activate the sheet. Post the following line so I can see what it is

Here's the whole thing:

Application.ScreenUpdating = False
Sheets("1537").Select
    Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").NumberFormat = "General"
    Range("I1").FormulaR1C1 = "Claim Type"
    Range("I2").FormulaR1C1 = _
        "=IF(RC[-1]=""Y"",""Specialty"",IF(RC[-2]=""Y"",""Retail 90"",IF(RC[-4]=""M"",""Mail"",""Retail"")))"
    Range("I2").Copy
    Range("H2").End(xlDown).Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("I1").Select
    Columns("I:I").Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select

This runs within a second, but I'm always up for more optimization