Find and insert page breaks... 1

[gjsaturday]

I have the following

I want create a macro that finds the word "total" in a specific column and inserts a page break immediately after that cell. Can this be done?

Thanks in advance.

 

[PHV]

Have you tried the macro recorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gjsaturday]

I've tried, but I don't know how to repeat the find and insert portion...

My recording generated this...

Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("12:12").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Columns("A:A").Select
Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate
Rows("16:16").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

How do I "loop" to continue finding and inserting until the end of my document?

TIA!

 

[FXP]

Try this:

Sub AddPageBreakAtTotal()
'''Adds page break after cell whose value is Total
Dim r As Range, cell As Variant
'''Set column range here
Set r = Range("A:A")

For Each cell In r
'''Set variable value to test
If cell.value = "Total" Then
cell.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell.Offset(1, 0)
End If

Next cell
End Sub

 

[gjsaturday]

Okay, but the cell value isn't exactly, "Total". It ends with the word total, but begins with varying Store numbers, i.e. "101 Total", "102 Total", "103 Total", etc...

Thanks again!

 

[FXP]

Replace the following line from:

If cell.value = "Total" Then

To:

If Right(cell.value,4) = "Total" Then


Be sure to spell Total exactly the same way each time.

 

[FXP]

Correction, I can't count. Change line to:

If Right(cell.value,5) = "Total" Then

 

[gjsaturday]

Beautiful! Thanks!