Page breaks on worksheet and Macros

[FM1]

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

 

[johnwm]

Why not just use the Header? View Menu|Header and footer

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[joerd]

The .HPageBreaks and .VPageBreaks methods of the Worksheet object are your friends. These return a HPageBreak or VPageBreak object, which has the useful property .Location returning a Range.
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.

Option Explicit

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.