Previous Topic Next Topic

Pulling ordered parts from sheet 2 and insert

Closed
AviationGuy - Jun 19, 2011 at 11:25 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 20, 2011 at 10:52 PM
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
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 20, 2011 at 10:52 PM
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
0