VBA to sequentially print upon single command

[cool0masty2]

I have this workbook where I paste data to, from an online source. Once the data is copied in the "DATA" Sheet, C2 is copied and pasted into "AWB"!R4, a print command is then sent. This goes on for C3, C4 ... until the next cell is empty.
I have automated this task a little. but what I really am looking for is to
1. Check the next row (suppose C5), if it's empty, then end/break the cycle.
2. Simplify the code using LOOP, as it will surely make the module lighter.

The workbook can be found here: DRTVs' AWB. Please download it and use Excel for any changes.

I can allow folder access so you can upload a new version of it. Let me know.

 

[pbornemeier]

Sub PrintLabels()

    Dim lLastDataRow As Long
    Dim lIndex As Long
    
    With Worksheets("AWB")
        .Select
        .PageSetup.PrintArea = Range("A1:N29")
    End With
    
    With Worksheets("Data")
        lLastDataRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        For lIndex = 2 To lLastDataRow
            .Cells(lIndex, 3).Copy Destination:=Worksheets("AWB").Range("R4")
            ActiveSheet.PrintOut Copies:=1
        Next
    End With
    
End Sub

 

[cool0masty2]

Sub PrintLabels()

    Dim lLastDataRow As Long
    Dim lIndex As Long
  
    With Worksheets("AWB")
        .Select
        .PageSetup.PrintArea = Range("A1:N29")
    End With
  
    With Worksheets("Data")
        lLastDataRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        For lIndex = 2 To lLastDataRow
            .Cells(lIndex, 3).Copy Destination:=Worksheets("AWB").Range("R4")
            ActiveSheet.PrintOut Copies:=1
        Next
    End With
  
End Sub

Thanks for your response.

I already made it work using the code I have copied below.
It's working exactly how I wanted it to be done.
Please take a look and let me know if you see any flaws in it?

Sub Print_AWB()
'
' Print_AWB Macro
' Prints AWB automatically.
'
' Keyboard Shortcut: Ctrl+Shift+B
'


i = 2
Do Until IsEmpty(Cells(i, 3))

        Worksheets("DATA").Select
Cells(R + i, 3).Select
Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Range("R4").Select
    ActiveSheet.Paste
        Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
ActiveSheet.Previous.Select


i = i + 1
Loop
End Sub

I have no understanding of VBA, but I somehow managed and wrote it on my own with little help from Macro Recorder, and Youtube.

 

[pbornemeier]

Worksheets("DATA").Select
should be before
Do Until IsEmpty(Cells(i, 3))
otherwise the Do Until may be looking at the wrong sheet

These 2 lines
ActiveSheet.Range("R4").Select
ActiveSheet.Paste

may be combined into
ActiveSheet.Range("R4").Paste

Otherwise if it works stay with it.

 

[cool0masty2]

This is what I have in the updated version now:

i = 2
Worksheets("DATA").Select
Do Until IsEmpty(Cells(i, 3))

        
Cells(R + i, 3).Select
Selection.Copy
    ActiveSheet.Next.Select
    Range("R4").PasteSpecial
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveSheet.Previous.Select


i = i + 1
Loop

Couldn't find any .Paste method, so I used .PasteSpecial, it works.

Updated the code as you suggested, found it to be more flawless.

 

[pbornemeier]

I was wrong with my earlier advice. (Did it from memory without checking). The following is tested:

You can replace

Cells(R + I, 3).Select
Selection.Copy
    ActiveSheet.Next.Select
    Range("R4").PasteSpecial
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveSheet.Previous.Select


I = I + 1

With

    Cells(R + I, 3).Copy Destination:=ActiveSheet.Next.Range("R4")
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    I = I + 1

The PasteSpecial command has arguments, for example:

    Range("F19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

 

[cool0masty2]

Cells(R + I, 3).Copy Destination:=ActiveSheet.Next.Range("R4") ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False I = I + 1

Not working as required.

 

[pbornemeier]

ActiveSheet.Next is not a good choice in this instance, use Worksheets("AWB") instead since it does not rely on the position of worksheets in the workbook.

Sub Print_AWB()

' Print_AWB Macro
' Prints AWB automatically.

' Keyboard Shortcut: Ctrl+Shift+B

    Dim I As Integer
    Dim R As Integer    'What is R for ?
    
    Worksheets("DATA").Select
    
    I = 2
    Do Until IsEmpty(Cells(I, 3))
        Cells(R + I, 3).Copy Destination:=Worksheets("AWB").Next.Range("R4")
        Worksheets("AWB").PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        I = I + 1
    Loop
    
End Sub