Get the line number of a page break using VBA

[Tom Ogilvy]

You can use the horizontal pagebreaks collection ( or vertical pagebreaks
collection for columns).

Sub Breaks()
Dim HPBreak As HPageBreak
Dim i As Long, j As Long
With ActiveSheet
Application.ScreenUpdating = False
j = ActiveSheet.HPageBreaks.Count
For i = j To 1 Step -1
Set HPBreak = ActiveSheet.HPageBreaks(i)
If HPBreak.Type = xlPageBreakAutomatic Then
Set HPBCell = HPBreak.Location
' do something with the pagebreak or relative to the pagebreak
End If
Next
End With
End Sub

-----------------

This uses xl4 macro commands to get the pagebreaks. Maybe you can adapt it
for you purpose:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ActiveSheet.DisplayPageBreaks = False
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""" & _
ActiveSheet.Name & """)"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""" & _
ActiveSheet.Name & """)"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
If Rows(horzpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, horzpbArray(j), brkType
Next j

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
If Columns(verpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, verpbArray(j), brkType
Next j
End Sub

Regards,
Tom Ogilvy

Luciano Morais <lcmo...@zipmail.com.br> wrote in message
news:1ca0d01c296e0$f62ed420$8df82ecf@TK2MSFTNGXA02...
> Hello,
>
> Is there a way to get which line will occur a page break
> when we show a preview or print the current sheet using
> VBA ? Because, I have a sheet that has some information
> sets with a different header for each set. I tried to use
> the PrintTitleColumns and PrintTitleRows property, but
> these properties is useful only when we have one
> header. I can't set manual page breaks, because those will
> vary depending on the printer's settings. Thanks in
> advance.
>
> Kind regards,
>
> Luciano