MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Range class error?

Posted by Donald on January 14, 2002 1:40 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
ProdCode = ActiveCell.Text
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?


Posted by Damon Ostrander on January 14, 2002 10:25 PM

Hi Donald,

I don't see what the cause of the error is, but I suggest you try eliminating all the unnecessary selections and see if that works. Here's the code without selections:

Private Sub CommandButton1_Click()
Dim ProdCode As String
ProdCode = Sheets("Clorox").[E4].Value
With Sheets(ProdCode).Rows(2)
.Insert Shift:=xlDown
End With