Having some odd print issues

Cryov

New Member
Joined
Aug 26, 2014
Messages
26
Hey fellas,
I got some helpful people on this forum to write me a code for printing out a massive amount of commission reports a few weeks back. However it only prints about 90% of the report in alphabetical order despite the excel doc having them alphabetized. I figure it may have something to do with the printer queue not being able to keep up with how fast this code spits out print jobs for each employee.
Here is the code:

Code:
Sub printTech()Dim techStartRow As Long
Dim lastRow As Long
Dim currRow As Long
 
 
lastRow = ActiveSheet.UsedRange.Rows.Count
For currRow = 1 To lastRow
    If Left(Cells(currRow, 1), 16) = "Technician Name:" Then
        techStartRow = currRow
    ElseIf Left(Cells(currRow, 1), 23) = "Totals Technician Name:" Then
        ActiveSheet.PageSetup.PrintArea = "$A$" & techStartRow & ":$L$" & currRow
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End If
Next

End Sub

And here is an link to an example doc of what I'm working on:
https://docs.google.com/spreadsheets/d/1Yd6tLVb1bQ9WHl_-zv8o2_TGasRckZH2slr6VKD3PHY/edit?usp=sharing

I am inexperienced at coding, best I can do is alter it slightly. Is there anyway to perhaps print it all at once without sending 1000s of individual jobs? Trick is to keep each employee separated so they can receive their own commission report. Perhaps a simple code to place a page break behind "Totals Technician Name:" ?
Any advice will help!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi Cryov,

Try to use “Wait” function for some seconds, my code is for 3 seconds
Before code “Next” add
Code:
 Application.Wait (Now + TimeValue("0:00:03"))
 
Upvote 0
In your example, all the techs have the same name, so I made their names alphabetical, and this ran as expected in PrintPreview:

Code:
Sub cryov()
    Const sBeg      As String = "Technician Name:"
    Const sEnd      As String = "Totals Technician Name:"

    Dim iRow        As Long
    Dim rBeg        As Range
    Dim rEnd        As Range

    With Range("A:A")
        For iRow = 1 To .Cells(.Rows.Count).End(xlUp).Row
            If Left(.Cells(iRow).Value, Len(sBeg)) = sBeg Then
                Set rBeg = .Cells(iRow)
            ElseIf Left(.Cells(iRow).Value, Len(sEnd)) = sEnd Then
                If Not rBeg Is Nothing Then
                    Set rEnd = .Cells(iRow)
                    .Worksheet.PageSetup.PrintArea = Range(rBeg, rEnd).Resize(, 12).Address
                    .Worksheet.PrintPreview
                    '.Worksheet.PrintPreview IgnorePrintAreas:=False
                End If
            End If
        Next iRow
    End With
End Sub

I didn't run it out to the printer.
 
Upvote 0
A tweak:

Code:
Sub cryov()
    Const sBeg      As String = "Technician Name:"
    Const sEnd      As String = "Totals Technician Name:"

    Dim iRow        As Long
    Dim rBeg        As Range

    With Range("A:A")
        For iRow = 1 To .Cells(.Rows.Count).End(xlUp).Row
            If Left(.Cells(iRow).Value, Len(sBeg)) = sBeg Then
                Set rBeg = .Cells(iRow)
            ElseIf Left(.Cells(iRow).Value, Len(sEnd)) = sEnd Then
                If Not rBeg Is Nothing Then
                    .Worksheet.PageSetup.PrintArea = Range(rBeg, .Cells(iRow)).Resize(, 12).Address
                    .Worksheet.PrintPreview
                    '.Worksheet.PrintPreview IgnorePrintAreas:=False
                    Set rBeg = Nothing
                End If
            End If
        Next iRow
    End With
End Sub
 
Last edited:
Upvote 0
I will have to give it a try monday and see how it goes. Thanks for the assist, ill give an update here if it works well for me.
 
Upvote 0
The "Application.Wait (Now + TimeValue("0:00:03"))" just freezes up everything, it sometimes prints the first sheet but otherwise excel just locks up.

shg, is there a way to get that code to print and stop showing me the previews? Because going 1 by 1 to manually print a couple thousand of these is what I am trying to avoid.
 
Upvote 0
is there a way to get that code to print and stop showing me the previews?
Comment out the PrintPreview line, and uncomment the next line.
 
Upvote 0
"IgnorePrintAreas:=False"
Had to take out the colon to get pass an error.

Code:
[/COLOR]Sub cryov()    Const sBeg      As String = "Technician Name:"
    Const sEnd      As String = "Totals Technician Name:"


    Dim iRow        As Long
    Dim rBeg        As Range


    With Range("A:A")
        For iRow = 1 To .Cells(.Rows.Count).End(xlUp).Row
            If Left(.Cells(iRow).Value, Len(sBeg)) = sBeg Then
                Set rBeg = .Cells(iRow)
            ElseIf Left(.Cells(iRow).Value, Len(sEnd)) = sEnd Then
                If Not rBeg Is Nothing Then
                    .Worksheet.PageSetup.PrintArea = Range(rBeg, .Cells(iRow)).Resize(, 12).Address
                    '.Worksheet.PrintPreview
                    .Worksheet.PrintPreview IgnorePrintAreas = False
                    Set rBeg = Nothing
                End If
            End If
        Next iRow
    End With

End Sub[COLOR=#574123]

If I follow your instruction, the worksheet.printpreview still shows up due to the command in the next row.
I tried deleting it out and just having IgnorePrintArea = False there but nothing happens when I run it. It acts like it is doing something but nothing prints.
 
Last edited:
Upvote 0
Sorry, that line should be

Code:
    .Worksheet.PrintOut IgnorePrintAreas:=False
 
Upvote 0

Forum statistics

Threads
1,223,520
Messages
6,172,804
Members
452,481
Latest member
Najwan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top