I am trying to create code that will look in one worksheet column for the
word Add, I then want it to copy certain fields in another worksheet based on
this. The code I have now will find the first unique field and copy it over,
I am trying to find a way to Vlookup that field and copy the rest of the
required fields. There are a total of 7 fields that I need to copy over,
including the first field, also the field locations on both worksheets vary.
Can anyone help me out. This is the code I have so far:

Sub NewOrders()
Dim FoundCell As Variant
Dim Job As range
Set FoundCell = Worksheets("Download").Columns("C").Find("Add")
Application.Calculation = xlCalculationAutomatic
Sheets("Download").Select
range(FoundCell.Address).Select
Do Until ActiveCell = "OK"
ActiveCell.Offset(0, 1).Select
Selection.Copy
Worksheets("MPS").Activate
ActiveCell.Offset(1, 0).Activate
ActiveCell.PasteSpecial _
Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd
Application.Calculation = xlCalculationAutomatic
Job = ActiveCell
ActiveCell.Offset(0, 1) = WorksheetFunction.VLookup(Job, range("D:Z"),
3, False)
ActiveCell.Offset(0, 2) = WorksheetFunction.VLookup(Job, range("D:Z"),
6, False)
ActiveCell.Offset(0, 3) = WorksheetFunction.VLookup(Job, range("D:Z"),
14, False)
ActiveCell.Offset(0, 4) = WorksheetFunction.VLookup(Job, range("D:Z"),
2, False)
ActiveCell.Offset(0, 8) = WorksheetFunction.VLookup(Job, range("D:Z"),
23, False)
ActiveCell.Offset(0, 12) = WorksheetFunction.VLookup(Job, range("D:Z"),
9, False)
Sheets("Download").Activate
ActiveCell.Offset(1, -1).Activate
Set FoundCell = Worksheets("Download").Columns("C").FindNext
Loop
End Sub
Thanks!