Page breaks on worksheet and Macros
Page breaks on worksheet and Macros
(OP)
I have a worksheet with page break view. I want to be able to have a macro recognize the begining and end of each page and write info to the pages begining at the top of each page.
Can this be done? Thanks
Can this be done? Thanks





RE: Page breaks on worksheet and Macros
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Page breaks on worksheet and Macros
For example, the following code gives you a function TotalPages, a function PageNo, and a procedure to display something at the bottom of the last page. Modify as you like.
CODE
Function TotalPages(R As Range) As Integer
Dim HPages As Integer, VPages As Integer
With R.Worksheet
HPages = .HPageBreaks.Count + 1
VPages = .VPageBreaks.Count + 1
End With
TotalPages = HPages * VPages
End Function
Function PageNo(R As Range) As Integer
Dim HPB As HPageBreak, VPB As VPageBreak, Wks As Worksheet
Dim HPages As Integer, VPages As Integer
Set Wks = R.Worksheet
HPages = 1
VPages = 1
For Each HPB In Wks.HPageBreaks
If HPB.Location.Row < R.Row + 1 Then HPages = HPages + 1
Next HPB
For Each VPB In Wks.VPageBreaks
If VPB.Location.Column < R.Column + 1 Then VPages = VPages + 1
Next VPB
If Wks.PageSetup.Order = xlDownThenOver Then
PageNo = (Wks.HPageBreaks.Count + 1) * (VPages - 1) + HPages
Else
PageNo = (Wks.VPageBreaks.Count + 1) * (HPages - 1) + VPages
End If
End Function
Sub InsertAtBottomOfLastPage()
Dim Wks As Worksheet, i As Integer, j As Integer
Set Wks = ActiveSheet
Wks.Cells(1, 2).Value = Wks.HPageBreaks.Count + 1
Wks.Cells(2, 2).Value = Wks.VPageBreaks.Count + 1
i = Wks.HPageBreaks(Wks.HPageBreaks.Count).Location.Row
j = Wks.VPageBreaks(Wks.VPageBreaks.Count).Location.Column
Wks.Cells(i - 1, j).Value = "This is the last page footer!"
End Sub
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.