Macro to Insert Page Breaks on Pivot Table (Excel 2003)

[rickincanada]

I currently have a a pivot table which groups by Period (column a) and then by Employee (column b). What I need to do is have a macro go through and insert a page break at the end of each employee.

My thinking is that I could look for each cell in column B that contains the word "Total" and then insert a page break 1 row below each of those. This sounds good in theory except I have no idea how to do this...if it is even possible.

Any assistance you can provide would be greatly appreciated. Thanks in advance - Rick.

 

[Saagar]

Use this code in a macro or a function block or a button code block

    oldrow = 0
    currow = 1
    Columns("B:B").Select
    searchstring = "Total"
    
    While oldrow < currow
        oldrow = currow
        On Error GoTo finish
        Selection.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        currow = ActiveCell.Row + 1
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(currow)
    Wend
    
    MsgBox ("It's Done Dude!")
    Exit Sub
    
finish:
        Err.Clear
        MsgBox ("Dude! search string not found :(")

 

[rickincanada]

Saagar - you're a beauty! Thanks so much - works perfect

 

[Saagar]

Glad to help. That's my one good deed for the day ....