VBA syntax for specifying page break locations based on cell contents

[chraco]

Hello,

I'd like to automate the process of moving page breaks within a fairly small pivot table. I just recorded the following macro while I manually adjusted page breaks to my liking. Here's the code:

ActiveWindow.View = xlPageBreakPreview
ActiveWindow.SmallScroll Down:=-33
ActiveSheet.HPageBreaks(3).DragOff Direction:=xlDown, RegionIndex:=1
Set ActiveSheet.HPageBreaks(2).Location = ActiveCell.Offset(-16, 0).Range("A1")
ActiveWindow.SmallScroll Down:=-87
Set ActiveSheet.HPageBreaks(1).Location = ActiveCell.Offset(-61, 0).Range("A1")
ActiveWindow.SmallScroll Down:=78
Set ActiveSheet.HPageBreaks(2).Location = ActiveCell.Offset(-9, 0).Range("A1")
ActiveWindow.SmallScroll Down:=-99

Rather than "ActiveCell.Offset(-61, 0). . ." etc., how can I instead specify a page break location based on the content of a cell? In the particular pivot table that I was working in, the top row (specified in the pivot table fields criteria) is LastName. So how could I specify page breaks at, for instance, Smith, Jones, Cooper?

Looking at the code and seeing "DragOff Direction," I'm guessing what I want to accomplish may be more complex, but I'll start there with my question.

Thanks so much for your time.

Raco

 

[Worf]

Hello

Sub PBreaks()
Dim pt As PivotTable, s$, sh As Worksheet


Set sh = ActiveSheet
s = "Jan"                               ' name you want


Set pt = sh.PivotTables("pt5")          ' the table


ActiveWindow.View = xlPageBreakPreview


Set sh.HPageBreaks(1).Location = pt.TableRange1.Find(s, , xlValues, xlWhole)


End Sub

 

[chraco]

Thanks Worf!

 

[Worf]

You are welcome!