Insert Page Break after finding "total" in specific column

[gbaker101]

I need a macro that will insert a page break each time it finds the string "total" in column D. The data starts in row 6, and as there are subtotals in more than one column, which is why I need it to insert only when "total" comes up in column D. Also, since it's been subtotaled, the verbiage varies (i.e., O1 Total, 02 Total, etc.). Thanks!

 

[VoG]

Try

Sub pgbrk()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If InStr(Range("D" & i).Value, "Total") <> 0 Then ActiveSheet.HPageBreaks.Add before:=Range("D" & i + 1)
Next i
End Sub

 

[gbaker101]

Thanks VoG...that did the trick!

 

[Kalvas]

Try

Sub pgbrk()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If InStr(Range("D" & i).Value, "Total") <> 0 Then ActiveSheet.HPageBreaks.Add before:=Range("D" & i + 1)
Next i
End Sub

VOG, I've been looking through the forums for something similar to this how would i go about adding a clause so that it only puts the page break every 3rd total. any help would be appreciated.

 

[VoG]

Maybe like this (untested)

Sub pgbrk()
Dim LR As Long, i As Long, j As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If InStr(Range("D" & i).Value, "Total") <> 0 Then
        j = j + 1
        If j = 3 Then
            ActiveSheet.HPageBreaks.Add before:=Range("D" & i + 1)
            j = 0
        End If
    End If
Next i
End Sub

 

[Kalvas]

That code worked beautifully, thanks.

but now i realized that the problem im actually having is the scale, it is trying to force it at 100% scale which is only allowing two total to be displayed on a page, so the automatic page breaks is inserted every two totals. is there any ways i can make the scale adjust so that it will always fit 3 totals on one page?

 

[VoG]

I suggest that you start a new topic for this - this thread is rather old.