Range class error?


Posted by Donald on January 15, 2002 2:18 PM

I am attempting to transfer data from one worksheet to another within a single workbook. The 'To' sheet is chosen based on a cell input on the 'From' sheet.

I am getting a "Run time error '1004' Select method of Range class failed" I stepped through to see what was happening and it is choosing the correct sheet, but then not selecting the row properly (error occurs at "Rows("2:2").Select).

Private Sub CommandButton1_Click()
Dim ProdCode As String
Sheets("Clorox").Select
Range("E4").Select
ProdCode = ActiveCell.Text
Sheets(ProdCode).Select
Rows("2:2").Select
Selection.Copy
Selection.Insert Shift:=xlDown

Basically, I want to shift the rows down and insert a new row at the top with test values transfered from the first sheet. I am copy and pasting because I need the formulae and formatting from the row before. Is there a better way to do this because my current understanding is that my error has to do with availible memory?

Thanks,
Donald

Posted by Barrie Davidson on January 15, 2002 2:33 PM

I haven't tested this, but you might try changing your code to:

Sheets(ProdCode).Rows("2:2").Copy
Sheets(ProdCode).Rows("2:2").Insert Shift:=xlDown

from:
Sheets(ProdCode).Select
Rows("2:2").Select
Selection.Copy
Selection.Insert Shift:=xlDown

The error message does not relate to memory. Excel is telling you that it can't select row 2 using that syntax.

Hope this helps you out.
BarrieBarrie Davidson



Posted by Donald on January 17, 2002 8:47 AM

Thanks, cleaning it up seemed to sort it out

Thanks