VBA Copy, Paste, Print, Loop next row

[chris186h]

Hello All. I have a sheet called 'Log'. Row 1 contains the Titles which do not need to have the macro run on.
Cells A:I have the data.
I am trying to get the code to copy the row, Paste it to 'Print out'A1
Print the document the return to the log sheet and repeat the code on the next row until there is nothing left to copy. The below code is what i have so far although it doesnt seam to paste the values and it always prints the titles.
Any help would be very appreciated
Thankyou

Sub PrintLogs()
    Dim lastRow As Long, i As Long
    Dim ws As Worksheet
    Set ws = Sheets("Log")
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws
        For i = 1 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            .Range("J" & i).Formula = "Printed"
 ActiveCell.EntireRow.Select
       Selection.Copy
    Sheets("Print-Out").Select
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
             Sheets("Log").Select
            
            
            
        Next i
    End With
End Sub

 

[MARK858]

Try the amendments below (untested so definitely test on a copy of your data).

Sub PrintLogs()
    Dim lastRow As Long, i As Long
    Dim ws As Worksheet
    Set ws = Sheets("Log")
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws
        For i = 2 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            .Range("J" & i).Formula = "Printed"
 .Range("A" & i).EntireRow.Copy
    Sheets("Print-Out").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Print-Out").PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Next i
    End With
End Sub

 

[chris186h]

Mark858 thankyou so much that worked exactally how i wanted it too.
Thanks for your help

 

[MARK858]

Your welcome (and amazed I didn't miss something ).

 

[kurigami]

Hi Mark858,

Hoping you can help me to expand this to work for me because I'm at a loss. I'm trying to do something similar, however, it requires a few extra steps. On my workbook I have two worksheets that the macro will be working between. Worksheet "Reps" has data in columns A:B with headers in Row 1 that need not be copied. The "Month" worksheet will be where the data is copying to. I would like the macro to copy cell B2 from the "Reps" worksheet into cell C4 on my "Month" worksheet and then copy cell A2 from the "Reps" worksheet into cell E4 on the "Month" worksheet. Once these are copied I would want range C7:J32 on the "Month" worksheet to Print to the default printer. This would then need to loop for however many rows contain data on the Macro sheet in columns A:B. Could you help out? Thanks!

 

[kurigami]

Hi Mark858,

Hoping you can help me to expand this to work for me because I'm at a loss. I'm trying to do something similar, however, it requires a few extra steps. On my workbook I have two worksheets that the macro will be working between. Worksheet "Reps" has data in columns A:B with headers in Row 1 that need not be copied. The "Month" worksheet will be where the data is copying to. I would like the macro to copy cell B2 from the "Reps" worksheet into cell C4 on my "Month" worksheet and then copy cell A2 from the "Reps" worksheet into cell E4 on the "Month" worksheet. Once these are copied I would want range C7:J32 on the "Month" worksheet to Print to the default printer. This would then need to loop for however many rows contain data on the Macro sheet in columns A:B. Could you help out? Thanks!

For the printing, I already have a macro established for that now that I think of it, so the new macro could simply run "Print_SnapShot" and then loop to the next row.

 

[chris186h]

Try the amendments below (untested so definitely test on a copy of your data).

Sub PrintLogs()
    Dim lastRow As Long, i As Long
    Dim ws As Worksheet
    Set ws = Sheets("Log")
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws
        For i = 2 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            .Range("J" & i).Formula = "Printed"
 .Range("A" & i).EntireRow.Copy
    Sheets("Print-Out").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Print-Out").PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        Next i
    End With
End Sub

Hello Mark858 or anyone else who may be able to help. I was wondering if this code can be modified in anyway to print out only cells that have print in them.
So modifying this code changing 'Printed' to 'Print' and it will run through the list following the same code but only print sheets with 'Print' in column J