Insert Page Break After Partial Text Match

[samcham]

Column A looks like this:

01.6000
01.6000
01.6000
01.6000
01.6000
01.6000
01.6000

Department A Total

01.6010
01.6010
01.6010

Department B Total

I need a macro to insert a horizontal page break AFTER each line where the
word "Total" appears in column A.

Any ideas??

- I'm not a programmer, just a user. So, I need rather explicit
instructions! ;-)

 

[ryguy7272]

I think this will do what you want:
Sub pagebreak()
Dim value1 As String

Range("A1").Select
For Each Cell In Columns("A").SpecialCells(xlCellTypeConstants)

If Cell.Value Like "*Total*" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add ActiveCell.Offset(1, 0)
Else
End If

ActiveCell.Offset(1, 0).Select
Next
End Sub

HTH!!

 

[Rick Rothstein]

I think this macro will do what you want...

Sub InsertPageBreaksForTotals()
Dim FirstAddress As String, C As Range
With Worksheets("Sheet2").Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set C = .Find("Total", LookIn:=xlValues, LookAt:=xlPart)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Offset(1).EntireRow.PageBreak = xlPageBreakManual
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
End Sub

To install the macro, press Alt+F11 from any worksheet to go into the VB
editor, click Insert/Module from the VB editor's menu bar and then
copy/paste the above code into the code window that appears. That's it... go
back to your worksheet and press Alt+F8 to get to the macro selection dialog
box, select InsertPageBreaksForTotals from the list and click the Run
button.

 

[samcham]

Ryan:

Thanks. The macro ran, but did not insert any page breaks.

Sam.

 

[ryguy7272]

Very bizarre. All I can think of is that you have some blanks in there. Try
this:
Sub pagebreak()
Dim MyCell As Range
For Each MyCell In Range("A1:A20")

If MyCell.Value Like "*Total*" Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add ActiveCell.Offset(1, 0)
Else
End If

ActiveCell.Offset(1, 0).Select
Next
End Sub

 

[samcham]

Thanks, Rick. That worked perfectly!

And Ryan...You're right, there are some blanks in Column A.