Load data into another worksheet based on cell value

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430
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.

Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
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

Active Member
Joined
Oct 18, 2004
Messages
430
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top