How to add page break based on keyword
Requirement:
The user is using the following code to insert pagebreaks based on changes in column D. The user would like to change it so that page breaks are inserted after the appearance of the word "Total" in column D. Note that the word total will typically be preceeded by other words. Example "Sales Total" or "G. Smith Total". If possible, the user would like to suppress this logic when the words "Grand Total" appear as the user would end up with a grand total page with nothing else on it.
Solution:
Code:
Sub hPageBreak()
Dim rTotal As Range
On Error Resume Next
With Sheet1 ' CodeName
.DisplayAutomaticPageBreaks = False
Set rTotal = .Range("D:D").Find(What:="Total", After:=.Range("D1"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rTotal Is Nothing Then
If rTotal <> "Grand Total" Then
Sheet1.HPageBreaks.Add Before:=rTotal
End If
End If
End With
On Error GoTo 0
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Davey Hawley.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
