Excel 2004 Pivot Table page breaks

[TLC]

Dear all. I would like to know if there's a way to page break after
each occurance of a specific column (change in field content) in a
pivot table (as you can choose to do so in a PC by choosing the Layout
option. Hopefully there is a way to do so, because it's great tool.

Thank you.

 

[Geoff Lilley]

I know exactly what you're talking about. There's no built-in way to
do it.

My code as listed below deals with a case where I have two column
fields in my PivotTable, and one row field:

Sub PTPB()
Dim intLastRow As Integer
Dim bytPageBreaks As Byte
' find where the words "Grand Total" are
Range("A1").Select
Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
' that cell is the last row
intLastRow = ActiveCell.Row
' if "A5" isn't the first cell, change accordingly
Range("a5").Select
'loop through the rows
Do Until ActiveCell.Row = intLastRow + 1
'if the second column is blank, then insert a page break
If ActiveCell.Offset(0, 1).Value = "" Then
ActiveCell.Offset(1, 0).Activate
Worksheets(ActiveSheet.Name).Rows(ActiveCell.Row).PageBreak
= xlPageBreakManual
End If
ActiveCell.Offset(1, 0).Activate
Loop
'remove the last page break, so that "Grand Total" isn't on its own
page
bytPageBreaks = ActiveSheet.HPageBreaks.Count
ActiveSheet.HPageBreaks(bytPageBreaks).Delete
End Sub

HTH

Cheers,
Geoff Lilley
Microsoft Office Master Instructor (2000/XP)
Apple Certified HelpDesk Specialist
(e-mail address removed)

 

[Geoff Lilley]

D'OH! I forgot to fix one line.

******FIXED*****

Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate

****FIXED***

 

[TLC]

Geoff, This is quite a fix -- and I appreciate your reply. I hope that
Microsoft is listening, because it would be much easier for all of us
to choose the page break command in layout!

Thank you.
Trudy