Printing Pages Via A Loop Printing Beyond What is Defined By The Loop

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Below is code that I use to print out a dynamic print range ...

Rich (BB code):
Sub printme_f()
    'define fields range
    rwtopLeft = Application.WorksheetFunction.VLookup("F", ws_lists.Range("X3:AD10"), 3, False)
    coltopLeft = Application.WorksheetFunction.VLookup("F", ws_lists.Range("X3:AD10"), 4, False)
    rwbtmRight = Application.WorksheetFunction.VLookup("F", ws_lists.Range("X3:AD10"), 5, False) + 43
    colbtmRight = Application.WorksheetFunction.VLookup("F", ws_lists.Range("X3:AD10"), 6, False) ' + 55
    
    Set fldrng = ws_front.Range(ws_front.Cells(rwtopLeft, coltopLeft), ws_front.Cells(rwbtmRight, colbtmRight))

    With ws_front.PageSetup
        ws_front.Unprotect
        .PrintArea = "J" & rwtopLeft & ":BM" & rwbtmRight
        pgnums = .Pages.Count
        ws_front.Protect
    End With
    ui1 = InputBox("Select:" & Chr(13) & "    (1)   SAVE Only" & _
        Chr(13) & "    (2)   PRINT Only" & _
        Chr(13) & "    (3)   PRINT & SAVE", "Output Selection")
    
    If ui1 = "1" Then
        savemef
    ElseIf ui1 = "2" Then
        ws_front.PrintOut
        ui1 = MsgBox("Pages printed: " & pgnums & Chr(13) & "Retrieve printed pages and return to printer tray, face down, top into machine." & Chr(13) & _
            "Press OK to launch print the reverse side of the signature sheets.", vbQuestion + vbOKCancel, "PRINT COMMAND")
        If ui1 = vbOK Then
            With Worksheets("Fld_Backside").PageSetup
                Worksheets("Fld_Backside").Unprotect
                .PrintArea = "A1:BC45"
                Worksheets("Fld_Backside").Protect
            End With
            ws_front.Protect
            For pg = 1 To pgnums
                With Worksheets("Fld_Backside")
                    .Visible = True
                    .PrintOut
                    .Visible = False
                End With
            Next pg
        End If
    Else
        ws_front.PrintOut
        ui1 = MsgBox("Pages printed: " & pgnums & Chr(13) & Chr(13) & "Retrieve printed pages and return to printer tray, face down, top into machine." & Chr(13) & Chr(13) & _
            "Press OK to launch print the reverse side of the signature sheets.", vbQuestion + vbOKCancel, "PRINT COMMAND")
        If ui1 = vbOK Then
        'Stop
            With Worksheets("Fld_Backside").PageSetup
                Worksheets("Fld_Backside").Unprotect
                .PrintArea = "A1:BC45"
                Worksheets("Fld_Backside").Protect
            End With
            ws_front.Protect
            For pg = 1 To pgnums
                With Worksheets("Fld_Backside")
                    .Visible = True
                    .PrintOut
                    .Visible = False
                End With
            Next pg
        End If
        savemef
    End If
    
End Sub

In my testing, suppose the user selects '3' to print the print range and then save that range as a new workbook. The print component seems to be giving me problems.
The print range appears to be defined properly. It results in 12 pages (pgnums) of the defined print area being printed. The user then prepares to print the static contents of another worksheet (worksheet "Fld_Backside" range("A1:BC45") onto the reverse side of the 12 sheets previously printed. The print range for fld_backside range "A1:BC45" is one page.

When the user presses ok to commence the printing to the back side, it prints as expected on the reverse of the previously printed pages, however, it prints an additional 12 pages of "Fld_Backside". I figured the loop would only print 12 pages, but it prints 24.

Can anyone suggest why it's printing twice as many as expected?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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