Rows that stick together

[rlink_23]

So some of you have already helped tremendously on this project, and it turned out great! I tested it live and I think I need to make a few changes as I found a few flaws in the design. And I am looking for some help. For example I have 3 main sheets (technically 7 as there is 5 days in the week.) But for all intensive purposes I have 3 sheets, ws1 = "Parts Master", ws2 = "Parts Order list", ws3 = "Monday". As it stands now I have the following code that runs on each day of the weeks sheet.

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("D6:D30")) 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
    
  
   
End Sub

As you can see, When data is entered in (D6:D30) on "ws3" the code finds the corresponding part number in the "ws1" and pastes the row into the next available row on "ws2". What I would like to change is when data is entered in "D6" on ws1 (first row of data on "Monday") it does the exact same thing but is only attached to Row 2 in "ws2" (first row of data in "Parts order list"). It still finds the data in "ws1" but I want "D6" on "ws3" and Row 2 in "ws2" to be together. this way if someone makes a mistake when they are entering data it only affects that one row. And can be easily fixed without having to erase all of the other data that has been entered. Then of course Move onto "D7" ws3 and row 3 "ws2". and all the way down to "D30" "ws3" and Row 26 "ws2" and I am hoping there is a way to do this. If someone can, please help me. I think this will take my workbook over the top and will be the best it can possibly be! If you have any questions please feel free to ask. I tried to make it as clear as possible. Somethings I feel like I don't do a very good job of that lol...

 

[JLGWhiz]

The code finds the value entered on Worksheet 3 in the rng on sheet 2 and puts that put the row of values (21 cells) onto the next available row of sheet 1. Not the way described in the OP. What you are suggesting to does not appear to be practical since sheet 2 appears to be static while sheet 3 appears to be in a constant state of change by user input.

 

[rlink_23]

Ur assumption seems correct, when data is entered in D6 on ws3 it finds the next available row and inputs the "found" data from ws1 onto ws2 then moving onto to d7 ws3 and Data entered in D7 does the same thing on the next avaliable row. All the way through D30.
What I was hoping to do was data entered on D6 ws3 would still input the "found" data from ws1 and only input it on row 2 of ws2 instead of next available row. Data entered in D7 ws3 would find data and only input in row 3. And so on all the way down to D30 and the corresponding row on sheet 2. Once that day is done and the parts order list is printed. It clears and then they move into the next day. I think I understand what u are saying tho.

 

[rlink_23]

The data On D6 ws3 and the next available row on ws2 will always be changing. Once the day is over ws2 will be cleared and the data entered on the next day sheet will again begin with D6 and so on and so forth. The only data that stays the same is the parts master list.
Essentially I was hoping to "marry" D6 on ws3 to Row 2 on ws2 so any data entered in D6 finds the corresponding data in ws1 and would always place it in row 2 of ws2. Then D7 places in row 3 and so on

 

[JLGWhiz]

Once more, the 'found' data comes from ws2 and not from ws1. According to your declarations, ws2 is the 'Parts Master', which I assume does is not changed by the user during an update. If you want to clear the user input from the daily sheets, that can be done with simple code added as shown below:

Set Rng = ws2.Range("A2:A" & LastRow)
Intersect(ws3.UsedRange, ws3.UsedRange.Offset(1)).ClearContents

since you are using worksheet event code, you would need to modify the code in each daily sheet. Be sure your ws reference is correct for the daily sheets if they are not all ws3.

 

[rlink_23]

Omg I'm an idiot. Ws1 and ws2 are backwards. The parts master sheet is ws1. And ws2 is the parts order list... I apologize. Although I don't think that changes anything... Jeez, I feel dumb.. ��