Copy fields, Print Sheet, Clear data and loop through populated rows

userzjkJ 0
Viewed 10

I have the following basic macro that copies some data from row 2 of sheet 'Query1' across to a sheet named 'Routing Card', then prints the Routing Card, and then clears the cells...

Sheets("Routing Card").Select
Range("A2:A3").Formula = "=Query1!D2"

Range("A5:A6").Formula = "=Query1!B2"
Range("A8:A9").Formula = "=Query1!C2"
Range("A11:A12").Formula = "=Query1!J2"
Range("B11:B12").Formula = "=Query1!AE2"
Range("B1:C9").Formula = "=A5"
Range("A15:C25").Formula = "=Query1!CH2"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
Range("A2:A3,A5:A6,A8:A9,A11:A12,B11:C12,B1:C9,A15:C25").Select
Range("A15").Activate
Selection.ClearContents

...what I need to do, is then copy the 3rd row from sheet 'Query1', print and clear cells, then row 4 etc. until all rows have been copied and printed. Sheet 'Query1' is populated from an Odata direct query so will have a different row count each time the query is refreshed.

Can anyone assist in explaining how I can loop through the rows on sheet 'Query1'?

Many thanks.

vba loops excel
1 Answers

Had a play with LastRow and Loops as suggested by findwindow. The following appears to work!

Sheets("Query1").Select

    Dim LastRow As Long, i As Long

    LastRow = Cells(Rows.Count, 2).End(xlUp).row
    For i = 2 To LastRow

Sheets("Routing Card").Select

Range("A2:A3").Formula = "=Query1!D" & i & ""
Range("A5:A6").Formula = "=Query1!B" & i & ""
Range("A8:A9").Formula = "=Query1!C" & i & ""
Range("A11:A12").Formula = "=Query1!J" & i & ""
Range("B11:B12").Formula = "=Query1!AE" & i & ""
Range("B1:C9").Formula = "=A5"
Range("A15:C25").Formula = "=Query1!CH" & i & ""

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
Range("A2:A3,A5:A6,A8:A9,A11:A12,B11:C12,B1:C9,A15:C25").Select
Range("A15").Activate
Selection.ClearContents

    Next i
userzjkJ0
Related