Macro Page Preview

[howard]

I have the following Code Below. The 1st page break should be row 49, but the break sows at row 44.

It would be appreciated if someone could amend my code so that 1st break is at row 49 and 2nd break to be up to last row containing data

 Sub Print_Preview()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim printRange As Range
  
    Set ws = Sheets("Summary")
  
    With ws
        ' Find the last row and column containing data
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        ' Define the print range starting from Column F to the last column
        Set printRange = .Range(.Cells(1, "F"), .Cells(lastRow, lastCol))
      
        ' Set up the page setup options
        With .PageSetup
            .PrintTitleRows = "$1:$1" ' Row 1 repeats at top
            .PrintTitleColumns = "$A:$A" ' Column A repeats on each page
            .Orientation = xlLandscape
            .Zoom = False ' Turn off automatic zoom
            .FitToPagesWide = 2 ' Fit to 1 page wide
            .FitToPagesTall = 2 ' Fit to 2 pages tall (70% scale)
            .PrintGridlines = True
          
            ' Headers and footers
            .LeftHeader = "&D&T"
            .CenterHeader = "Turnover Data"
            .RightHeader = "Page &P of &N"
        End With

        ' Reset all existing page breaks
        .ResetAllPageBreaks
      
        ' Insert a manual horizontal page break at row 49
        .HPageBreaks.Add Before:=.Cells(53, "F")

        ' Activate the sheet and display Page Break Preview
        .Activate
        ActiveWindow.View = xlPageBreakPreview
      
        ' Set the print area for the defined range
        .PageSetup.PrintArea = printRange.Address
      
        ' Show Print Preview
        printRange.PrintPreview
    End With
End Sub [code]

 

[Micron]

I'm guessing that you have some multiple of 44 rows, like 88, 132, 176? I think your data is being divided evenly into portions, then because you spec 2 pages you're getting a break at 44 (and maybe 88). Does that make sense?

I used your code on a sheet with 120 rows. The breaks are at 60 and 120. Each time I added blocks of rows, the break row is further down but always divided by 2.

 

[howard]

Thanks for the help. I created this additional code which works

 Sub Adjust_PageBreak_Row49()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Define the worksheet
    Set ws = Sheets("Summary")

    With ws
        ' Find the last row with data
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

        ' Reset all existing page breaks (this will remove the dotted blue lines as well)
        .ResetAllPageBreaks

        ' Add a manual horizontal page break at row 49
        If lastRow > 49 Then
            .HPageBreaks.Add Before:=.Rows(50)
        End If

        ' Add a manual horizontal page break after the last row of data
        If lastRow > 49 Then
            .HPageBreaks.Add Before:=.Rows(lastRow + 1)
        End If

        ' Ensure page setup is configured properly
        With .PageSetup
            .Orientation = xlLandscape
            .PrintGridlines = True
            ' Disable automatic scaling to avoid conflicts
            .FitToPagesWide = False
            .FitToPagesTall = False
        End With
 Set ActiveSheet.HPageBreaks(1).Location = Range("F50")
        ' Switch to Page Break Preview to confirm the change
        .Activate
        ActiveWindow.View = xlPageBreakPreview
    End With
End Sub

 

[Micron]

Glad you solved it!