Insert page break/row before every occurence of a specific word

[AdRock]

I'm hoping someone here will be able to help me out with a small problem.

I have a worksheet with (I don't know how to descibe it) a list of tables.
Each table has a list of headings.

What i want to do is, create a macro the will look in column A look for every occurence of a specific word, first add a page break above then after the page break has been inserted, insert 30 blank rows.

What i need this for is, that I will paste an image manually in the 30 row gap and then have the data underneath the image. It will eventually show a seperate page for each image along with it's data.

I know how to insert rows/page breaks but not before where i find my specified word.

Any help greatly appreciated.

 

[Domenic]

Somewhat unclear... Let's assume that A10 contains the first occurrence of your specific word. Where do you want your page break? Do you want it above A10 or above A9? Also, at which cell do you want to start inserting your 30 rows?

 

[AdRock]

Thanks for the reply.

Say A10 has the first occurence, I would like a page break between A9 and A10 so the first row on the page would be my word.

After the page break has been done, I need to insert 30 rows above my word.

 

[Domenic]

Does this return the desired result?

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FirstAddress [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] PrevAddress [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] CurrAddress [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] SearchTerm [color=darkblue]As[/color] [color=darkblue]String[/color]

    SearchTerm = "A"

    [color=darkblue]With[/color] Columns("A")
        [color=darkblue]Set[/color] FoundCell = .Find(SearchTerm, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            FoundCell.Name = "FirstAddress"
            [color=darkblue]Do[/color]
                PrevAddress = FoundCell.Address
                FoundCell.Resize(3).EntireRow.Insert
                ActiveSheet.HPageBreaks.Add before:=Range(PrevAddress)
                [color=darkblue]Set[/color] FoundCell = .FindNext(FoundCell)
            [color=darkblue]Loop[/color] [color=darkblue]While[/color] FoundCell.Address <> Range("FirstAddress").Address
        [color=darkblue]Else[/color]
            MsgBox "No search term found...", vbExclamation
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[AdRock]

Many thanks

That did the trick exactly what i wanted