Pulling ordered parts from sheet 2 and insert

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

What I'm attempting to do is create a master list of items ordered and what they are ordered for.

Sheet 1 is a list of tasks generated by our mechanics. EX:

TASK DISCREPANCY
A 299 L/H Window cracked
B 300 Corrosion on pax dr
C 301 R/H fuel line leaking

We then order parts and our parts dept enters this data to another spreadsheet. EX:

TASK QTY ITEM P/N
A 299 1 Window 42552-42
B 300 5 Brushes 435321
C 301 5 Brushes 435321
D 300 1 Pax angle s4252-597
E 299 10 Spring washers nas1498-64

What I want to do is be able to automatically combine each list every morning. Both lists grow daily and only the tasks list is in order. With this example, I'd like to see this result

Task QTY DISCREPANCY/ITEM P/N
A 299 L/H Window cracked
B 299 1 Window 42552-42
C 299 10 Spring Washers nas1498-64
D 300 Corrosion on pax dr
E 300 5 Brushes 435321
F 300 1 Pax angle s4252-597
G 301 R/H fuel line leaking
H 301 5 Brushes 435321

Any help would be much appreciated.

Thanks,

Jay

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
the letter A,B etc are not clear . are they row indicators. anyhow I ignore those letters . I have also deleted column heading ROW from sheet 1 and sheet 2. try this macro. (If necessary modify the macaro)

sub test() 
Dim dest As Range, j As Integer 
For j = 1 To 2 
Worksheets(j).UsedRange.Copy 
With Worksheets("sheet3") 
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) 
dest.PasteSpecial 
End With 
Next j 
With Worksheets("sheet3") 
.UsedRange.Sort key1:=Range("A1"), header:=xlNo 
.Range("A1") = "task" 
.Range("B1") = "quantity'" 
.Range("c1") = "descrepancy" 
End With 
End Sub