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!
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!