Page break code - hpagebreak before value

[Maarkr]

Need to have a page break before every instance of *dept* in a column... this
doesn't work. Do multiple columns mess it up?

Cells.PageBreak = xlPageBreakNone
col = 1
LastRw = 3300
For x = 1 To LastRw
If Cells(x, col).Value = "*dept*" Then 'or like "*dept*"
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(x)
End If
Next
ActiveWindow.View = xlPageBreakPreview
'ActiveSheet.ResetAllPageBreaks
End Sub

 

[Jacob Skaria]

Try the below....Have you tested the other macro...with the sample posted...

Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1
If lngRow <> 1 Then
If UCase(Right(Range("A" & lngRow), 5)) = " DEPT" Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & lngRow)
End If
End If
Next
End Sub

If this post helps click Yes

 

[Maarkr]

it won't work because the text 'dept' is in a different place thruout the
cell... it may be =678899 UFFH Finance Dept ****** or =**884 Marktg Dept
Anytown MI
so the Right(Range("A" & lngRow), 5)) = " DEPT" won't work... can't I use a
wildcard???

 

[Jacob Skaria]

OK. Try the below

Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1
If lngRow <> 1 Then
If Instr(1, Range("A" & lngRow), " DEPT", vbTextCompare) > 0 Then
ActiveSheet.HPageBreaks.Add Before:=Range("A" & lngRow)
End If
End If
Next
End Sub


If this post helps click Yes