VBA- Inserting PageBreaks: Variable quantity, dynamic ranges

menschmaschine

New Member
Joined
Dec 21, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
The code below is meant to insert pagebreaks at certain rows based on the cell value in column A = "^". On Print Preview, it has the right number of pages, but just repeats the first page on all pages, instead of printing the area for each pagebreak. When I do this manually, it works fine, but I don't understand why the code makes it repeat the first page for all pages. Anybody have an idea what's wrong here?

VBA Code:
Sub Print_UnitSchedule()
    Dim USch As Worksheet
    Dim ShRowLast As Long, i As Long
    Dim ShCol As Range, PRngStrt As Range, PRngEnd As Range, UPrtRng As Range
    Application.ScreenUpdating = False
    
    Set USch = Sheets("Unit Schedule")
    USch.ResetAllPageBreaks
    Set PRngStrt = USch.Range("A7")
    ShRowLast = USch.Range("A" & Rows.Count).End(xlUp).Row
    Set PRngEnd = USch.Range("AE" & ShRowLast)
    Set UPrtRng = Range(PRngStrt, PRngEnd)
    Set ShCol = USch.Range("A7:A" & ShRowLast)
    
    For i = 7 To ShRowLast
        If ShCol.Cells(i, 1).value = "^" Then
            USch.HPageBreaks.Add Before:=ShCol.Cells(i, 1)
        End If
    Next i
    
    With USch.PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .Orientation = xlLandscape
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With

    UPrtRng.PrintPreview
    USch.ResetAllPageBreaks
            
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Nevermind. Answered my own question. It worked all along. For some reason, print preview showed a repeated first pagebreak print area on every page. But when actually printing, it prints each pagebreak area on each page as intended. Not cool, Microsoft!
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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