Insert pagebreak when a specific word appears in a column.

[Neal]

Hi,
I am importing a spreadsheet from another application which has the word
"Account" appearing multiple times in the first column.
I would like to create a Macro that will search for each occurance and
insert a page break above the row containing the word.
Not sure if this is possible?
Any help would be greatly appreciated.
Thanks, Neal...

 

[Sheeloo]

Try the macro
Sub insertBreak()
Dim c As Range, s As Long
lastRow = ActiveSheet.Cells(65000, "A").End(xlUp).Row
Range("A1:A" & lastRow).Select
Set c = Selection.Find("Account")
Rows(c.Row).PageBreak = xlPageBreakManual
End Sub

It will insert a break before the first occurence of "Account" on the active
sheet

 

[Gord Dibben]

Option Compare Text
Sub Insert_PBreak_Col()
Dim rng As Range
Dim Cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns(1))
For Each Cell In rng
If Cell.Text = "Account" Then
Cell.PageBreak = xlPageBreakManual
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Neal]

Hi Gord,

This worked great! thanks for your help on this.

Cheers, Neal...

 

[Gord Dibben]

Good to hear.

Thanks for the feedback

Hi Gord,

This worked great! thanks for your help on this.

Cheers, Neal...