×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Page breaks on worksheet and Macros

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

RE: Page breaks on worksheet and Macros

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: www.essexsteam.co.uk

RE: Page breaks on worksheet and Macros

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.

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources