Good morning all:

Long time listener, first time caller......


I am a rank beginner when it comes to VBA code, and can't figure out how to perform a specific loop within the following macro code that I have for a work project. My question will appear after the code shown below:



Sub SalesOrderChangeAutomation()
'
' SalesOrderChangeAutomation Macro
' Macro recorded 12/31/2004 by Me
'


Do

'The code below copies and pastes my Access query output data into the sales order data input sheet
Sheets("Query Output").Select
Range("A2:D2").Select
Selection.Copy
Sheets("Data input").Select
Range("D3:D6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Query Output").Select
Range("E2:G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data input").Select
Range("D9:D11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Query Output").Select


'The data below is potentially repeated on a single sales order change, but I can't figure out how to write the loop to capture multiple lines.
Range("H2:N2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data input").Select
Range("B15:H15").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Query Output").Select
Range("O2:T2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data input").Select
Range("K15:P15").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Query Output").Select
Range("U2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data input").Select
Range("B39").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'The code below deletes Row 2 from the Query Output sheet after it's line-data has been copied and pasted into the data input sheet.
Sheets("Query Output").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp

'The code below prints the Sales Order Change Form
Sheets("Order Change").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


'The code below clears the contents of the data entry sheet for the next Sales Order
Sheets("Data input").Select
Range("D3:D11").Select
Selection.ClearContents
Range("B15:I27").Select
Selection.ClearContents
Range("K15:Q27").Select
Selection.ClearContents
Range("B39:H39").Select
Selection.ClearContents
Range("A1").Select

'The code below ends sales order changes once the final row of data from the query output screen has been completed.
Sheets("Query Output").Select
Loop Until IsEmpty(Range("D2"))


End Sub


This macro simply copies selected cells from rows of data from one sheet (the "Query Output" sheet), and pastes them onto a pre-made order entry form here at work (the "Data Input" sheet).

This macro works if I only change ONE LINE at a time on any given sales order. If I have two lines that change on one sales order, then it prints each line/change out on an individual page (2 sheets).

However, many of our changes will require changing two or three (up to 13 lines) on sales order. The first reference to the sales order number is cell D2 on the "Query Output" sheet . So, I need the macro to look below to cell D3 and see if the sales order number matches that in D2. The same look-up would continue down the sheet (to D4, D5, etc), and ultimate would ensure that for all similar sales order numbers those rows/cells of pertinent data are copied onto one "Data Input" form. Essentially, I want to copy/paste all 'lines' from 'each' sales order onto one Sales Order Change form, then print that form and go onto the next Sales Order Number for it's changes.

I hope my question made sense, but if I need to clarify things, please feel free to ask questions. I have made several (very very bad) attempts to loop this on my own, and did some very bad bad things, so I'm hoping some of you are willing/able to assist.

Thank you in advance for your time and consideration.