Page Break on Pivot Table when printing - Is it possible to only page break at a blank row IF it goes over 1 page

[Goodluckboss]

Hey guys I have a pivot table that has groups of items. For example Group 052 has 3 items in it --> 1 blank row --> Group 053 has 5 items in it --> 1 blank row

If group 53 is cut off with a page break, is it possible to instead print up to group 0

52 and continue Group 053 in a new page?

 

[Domenic]

The following code will loop through each horizontal pagebreak, starting at the top. For each pagebreak, if there's a value on both sides, it moves the pagebreak upward until it finds a blank row. Note that it assumes that the worksheet containing the data is the active worksheet.

Option Explicit

Sub AdjustHorizontalPageBreaks()

    Dim originalView As XlWindowView
    Dim currentPageBreak As HPageBreak
    Dim currentLocation As Range
    Dim pageBreakCount As Long
 
    With ActiveWindow
        originalView = .View            'remember view for active window
        .View = xlPageBreakPreview      'change view to xlPageBreakPreview needed to adjust the pagebreaks
    End With
 
    pageBreakCount = 1
 
    On Error Resume Next
    Set currentPageBreak = ActiveSheet.HPageBreaks(pageBreakCount)
    If Not currentPageBreak Is Nothing Then
        Do
            Set currentLocation = currentPageBreak.Location
            If Len(currentLocation.Offset(-1, 0)) > 0 Then
                If Len(currentLocation) > 0 Then
                    Do
                        Set currentLocation = currentLocation.Offset(-1, 0)
                        If currentLocation.Row = 1 Or currentLocation.EntireRow.PageBreak <> xlPageBreakNone Then
                            Exit Do
                        End If
                    Loop Until Len(currentLocation) = 0
                    If currentLocation.Row > 1 Then
                        If currentLocation.EntireRow.PageBreak = xlPageBreakNone Then
                            Set currentPageBreak.Location = currentLocation
                        End If
                    End If
                End If
            End If
            Set currentPageBreak = Nothing
            pageBreakCount = pageBreakCount + 1
            Set currentPageBreak = ActiveSheet.HPageBreaks(pageBreakCount)
        Loop Until currentPageBreak Is Nothing
    End If
    On Error GoTo 0
 
    ActiveWindow.View = originalView    'change back to the original view
 
End Sub

Hope this helps!