Data in a cell in Sheet 3, pulls row with matching part number in sheet 1, and places it in sheet 2

[rlink_23]

Boy do I have a dooze!!! lol, I am having trouble figuring this out and would love some help!

SO I have this code that I was using that you would put a number in a cell on sheet 1, it would pull the data from sheet 2, and put it int he cells I had set with offest. Now i would like to do the same, except add another sheet into the equation.

Sheet 1 = Parts Order List

• Generally lists are numbered 1-25 With headings and such Starts
• This will have the parts of each daily list that gets built

Sheet 2 = Parts Master (master sheet where all the parts are listed for a specific Product)

• All the Product number is in Row A and then the parts go from cells B thru Sheet

Sheet 3 = Daily Build list (Products being built that day)

• This also is listed 1-25
• This is the list that I make every day that has all of the product being built that day

The only part of this sheet that matters is Row "D" Starting at "D6" Which is #1 , and goes down from there to 25 ("D30")

What I am trying to do is When the "Part Number" is typed into "D6" - "D30" it matches the part number in the "Parts Master and transfers all that row into row 1 on The parts "Order list". then moving onto the next row in both Parts order list and Daily Build list to add another part number and so on.... Several Cells also have "lists done through Data validation. I don't know if i am asking too much of Excel or not. But this is what we have been using for years to build out build lists and I was tasked with doing this within Excel.
I have all the values entered for the parts Master which was lengthy in itself...

The code I have started is this. But it errors out on this line below

If Not Intersect(Target, ws3.Range("D6")) Is Nothing Then

Private Sub Worksheet_Change(ByVal Target As Range)Dim LastRow As Long
Dim Rng As Range, Found As Range
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet




Set ws1 = Sheets("Parts Order list")
Set ws2 = Sheets("Parts Master")
Set ws3 = Sheets("Monday")
LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = ws2.Range("A3:A" & LastRow)


If Not Intersect(Target, ws3.Range("D6")) Is Nothing Then
    Set Found = Rng.Find(what:=Target.Value, LookIn:=xlValues)
    If Not Found Is Nothing Then
     ws1.Range("B1") = Found.Offset(0, 1)
        ws1.Range("B2") = Found.Offset(0, 2)
        ws1.Range("B3") = Found.Offset(0, 3)
          ws1.Range("B4") = Found.Offset(0, 4)


    End If
    
    
    End If
   
End Sub

Any help would be greatly appreciated!!! Thank you in advance!!!

 

[James006]

Hello,

It seems your working range is D6:D30 ...

If so ... you could amend your instruction as follows :

[COLOR=#333333]If Not Intersect(Target, ws3.Range("D6:D30")) Is Nothing Then[/COLOR]

Hope this will help

 

[rlink_23]

Yes I thought about that but was trying to just get the code to work so was focusing on making it work and was going to add that. But it won't work.. :/

 

[rlink_23]

Also, I am not sure which page to paste this code on. Does it go on the page I am trying to build this code for? Or do I build a module? I have tried it in a few spots and still won't run

 

[James006]

Hello,

All Event macros have to be stored in the respective Sheet's module ...

HTH

 

[rlink_23]

So I want the data to be entered in ws1 so put the event macro in that sheet module? Which is what I did. But want to verify

 

[rlink_23]

So playing with this a little bit more I am getting the error VBA: Runtime Error Method 'Intersect' of object '_Global' failed when I run this line of code.

If Not Intersect(Target, ws3.Range("D6")) Is Nothing Then

Interestingly it is transferring the part number but is putting it in B1 and not B2 like it should be (and yes I changed this line of code to match but to no avail)

 ws1.Range("B1") = Found.Offset(0, 1)
        ws1.Range("B2") = Found.Offset(0, 2)
        ws1.Range("B3") = Found.Offset(0, 3)
          ws1.Range("B4") = Found.Offset(0, 4)

 

[rlink_23]

Ok so far I can put the part number in ("D6") on "Monday" and if finds the part number in "Parts Master" and transfers that whole row in the "Parts order list"; I added ("B6:B30") to the code and all of them work. But what I need to do is once a part number is put on Row A, the code needs to move to the next row. and so on. Basically the "Monday" list, when Part Number is added in (D6:D30) will match the "Parts Order List" Row 1 on "Monday" will hold the Parts pulled from "Parts Master" and insert on Row 1 in Parts Order List" and move on to # 2 and do the same thing... Any ideas? Anyone? lol If you wnat to see how far I hve gotten on my code this is what I have so far...

Private Sub Worksheet_Change(ByVal Target As Range)Dim LastRow As Long
Dim Rng As Range, Found As Range
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet




Set ws1 = Sheets("Parts Order list")
Set ws2 = Sheets("Parts Master")
Set ws3 = Sheets("Monday")
LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = ws2.Range("A2:A" & LastRow)


    If Not Intersect(Target, ws3.Range("D7")) Is Nothing Then
    Set Found = Rng.Find(what:=Target.Value, LookIn:=xlValues)
    If Not Found Is Nothing Then
     ws1.Range("B3") = Found.Offset(0, 0)
     ws1.Range("C3") = Found.Offset(0, 1)
     ws1.Range("D3") = Found.Offset(0, 2)
     ws1.Range("E3") = Found.Offset(0, 3)
     ws1.Range("F3") = Found.Offset(0, 4)
     ws1.Range("G3") = Found.Offset(0, 5)
     ws1.Range("H3") = Found.Offset(0, 6)
     ws1.Range("I3") = Found.Offset(0, 7)
     ws1.Range("J3") = Found.Offset(0, 8)
     ws1.Range("K3") = Found.Offset(0, 9)
     ws1.Range("L3") = Found.Offset(0, 10)
     ws1.Range("M3") = Found.Offset(0, 11)
     ws1.Range("N3") = Found.Offset(0, 12)
     ws1.Range("O3") = Found.Offset(0, 13)
     ws1.Range("P3") = Found.Offset(0, 14)
     ws1.Range("Q3") = Found.Offset(0, 15)
     ws1.Range("R3") = Found.Offset(0, 16)
     ws1.Range("S3") = Found.Offset(0, 17)
     ws1.Range("T3") = Found.Offset(0, 18)
     ws1.Range("U3") = Found.Offset(0, 19)
     ws1.Range("V3") = Found.Offset(0, 20)
     
       


    End If
    
    
  End If
End Sub