Condensing code, and moving to the next line in the sheet.

[rlink_23]

So this code does what I want it to (mostly) the only thing that is missing is a way to move onto the next line down in the program. This sub right now, finds and pulls the row in "Parts Master" after inputting the part number in ("D6") on "Monday" and puts the data in Row "A" on "Parts Order List". It should move onto the next row in both "Parts Order List" to be ready to accept the part number in the next row on "Monday" sheet which would be ("D7"). There has to be a way to condense the code I have written and to move on to the next line for input. I'm not proficient enough to know how to do this. Is anybody available to help me? I know I am asking a lot, but I appreciate any help!!! thank you in advance!!!!

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

 

[Kamolga]

Hi,

You can replace

 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) [COLOR=#333333]    
     ws1.Range("V3") = Found.Offset(0, 20)
end if
[/COLOR]

by

Dim i As Long
 
   If Not Found Is Nothing Then
   For i = 0 To 20
      ws1.Cells(3, i + 2) = Found.Offset(0, i)
   Next i
end if

 

[Fluff]

Alternatively try

   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("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 21).Value = Found.Resize(, 21).Value
      End If
   End If

 

[rlink_23]

KAMOLGA!!!!!! This worked marvelously!!!! Thank you so much. I maybe should start a new thread but I am trying to get a cell that has data validation (list) to move to the page when this above code runs. Is this even possible?

I'm sure yours would have worked as well Fluff! But maybe u can help wm with this next dilemma!!!

 

[Kamolga]

KAMOLGA!!!!!! This worked marvelously!!!! Thank you so much

You are welcome, thank you for the feedback.

I am trying to get a cell that has data validation (list) to move to the page

What do you mean move to the page?
From where to where? I would simply cut and paste it...

 

[rlink_23]

Well what I mean is when I add part number in D6:D30 on "Monday" and then it pulls the data from "Parts Master" sheet and pastes it into "parts Order List". In the parts master sheet it has multiple options for a few parts, and that's where the drop down lists come from. I was thinking an if statement in the code above you so kindly gave me which didn't work that when data validation is present in the Parts master list, and that part number is called that it transfers the drop down list via vba. Does that make sense? BTW! I really am so grateful for you and the bit of code above. I am still learning (obviously) and you save t a bunch of time for sure!!!

 

[Kamolga]

If what you want is to have the drop down list that expends (so you have more lines in the dropdown list because you added something),

Then, you sould select te area where the list come from, click on insert tab and table (in design tab, you can make it look like it was before).
Now you select the content o te table without the title, go on formula tab and click define name. Let's call this PartList and it should be refering to something like

=Table1[Column1]

Now in your dropdownlist, for the source you replace something like =$A$1:$A$10 by =PartList. From now on, everytime you put someting in A11, A12, etc. it will automatically come in your list