Load data into another worksheet based on cell value

[Ron Morris]

Hi,

I am trying to load data from one worksshet to another based on criteria in a cell. I used the following code for test purposes but am getting a Run Time Error '1004', Select Method of Range Class Failed. I have read the help feature and as far as i can tell, I meet on the criteria to make this work.

Private Sub cmdtest_click()
Dim routingrow

If Sheets("sheet1").Range("c18").Value = 1 Then
Set routingrow = Sheets("sheet3").Range("A50").End(xlUp)

Sheets("sheet3").Range("A31").Select
routingrow.Offset(1, 0).Value = Sheets("Sheet1").Range("a4").Value
routingrow.Offset(1, 2).Value = Sheets("Sheet1").Range("c4").Value
routingrow.Offset(1, 4).Value = Sheets("Sheet1").Range("d4").Value
routingrow.Offset(1, 5).Value = Sheets("Sheet1").Range("e4").Value
routingrow.Offset(2, 0).Value = Sheets("Sheet1").Range("a4").Value
routingrow.Offset(2, 2).Value = Sheets("Sheet1").Range("g4").Value
routingrow.Offset(2, 4).Value = Sheets("Sheet1").Range("h4").Value
routingrow.Offset(2, 5).Value = Sheets("Sheet1").Range("i4").Value
routingrow.Offset(3, 0).Value = Sheets("Sheet1").Range("a4").Value
routingrow.Offset(3, 2).Value = Sheets("Sheet1").Range("k4").Value
routingrow.Offset(3, 4).Value = Sheets("Sheet1").Range("l4").Value
routingrow.Offset(3, 5).Value = Sheets("Sheet1").Range("m4").Value
End If
End Sub

I was using this for test purposes but will need to change to For / Each / Case structure since I will have multiple possibilities.

Any insight to this issue for this newbie would be greatly appreciated.

Thank you,

Ron

 

[Artemus]

Part of your problem is that the line

Set routingrow = Sheets("sheet3").Range("A50").End(xlUp)

is setting the variable equal to the value contained in the resulting location rather than the address of that cell. When you try to do an offset you are doing an offset from a value and not a range. That causes an error.

I am also confused as to why you are selecting cell A31 on sheet3. If you are going to select a cell on sheet3 why not just select the results of the first .End(xlUp) and then do the offsets from ActiveCell?

 

[Ron Morris]

Hi Artemus,

Thanks for the response.

Like I said, I'm a newbie!!! I was looking at something I had used for a workbook and was trying to alter it for this.

I was getting the run time error on the Sheets("sheet3").range("A31").Select line. Sorry, I forgot to put the line in my message but i forgot.

I thought I would need this line to get me to Sheet3 where the data would then be copied.

Please let me know what I should be doing.

Thanks,

Ron

 

[Ron Morris]

Hi Artemus,

I figured it!!!

Thanks for the response though.

Ron