Page break AFTER data is found not BEFORE

[GA3]

Can this be altered to put the page break after it finds the data entered instead of finding the data and backing up 1 row and inserting the page break?

I am using it to create page breaks of data on a pivot table. The data I am searching for is "*Total". The reason for the asterisk is because the pivot table puts in this case the store number and then Total. I have found that the asterisk works as a wild card.

The issue is that once it finds "*Total" it puts the page break on top of that row which puts the total on the top of the next page which is a different store.

Here's the code i am using:

Sub PageBreaks()

Dim c As Range
Dim FirstAddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String

Prompt = "What do you want to search for?"
Title = "Search Term Input"
Search = InputBox(Prompt, Title)
If Search = "" Then
Exit Sub
End If
With ActiveSheet.UsedRange
Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Sub

Can this be changed to move the page break down 1 row?
Thanks!

 

[anthonya2369]

Try changing these lines:

Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

AND

Set c = .FindNext(c)

To:

Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Offset(1,0)

AND 

Set c = .FindNext(c).Offset(1, 0)

 

[GA3]

Anthony,

You are awesome. It works perfectly!