Putting pagebreaks after objects

[F*SH]

Hi again

I've got a page that has lots of charts on it, stacked above each other
vertically. When I want to print the page, Excel has a habit of cutting
the occasional unlucky graph in half

So, to help stop this needless charticide, I want to add some page
breaks. Specifically, one after every 8 charts. This I thought would be
easy when I saw the HPageBreaks property. Great, I thought, after every
8th chart I'll just add one of these with

Code:
--------------------

ActiveSheet.HPageBreaks.Add After:=ActiveChart

--------------------


Naturally, it wasn't so cooperative, and gave me big, unrecoverable
errors. Any idea why? Presumably it's because it wants a cell, rather
than an object, as a location reference. Which suggests another way -
is there any way to determine the row onwhich the chart's lower edge
can be found? Then I could add a pagebreak to a cell on this row, which
will probably be more agreeable to VB.

Thanks.

 

[Mike Fogleman]

This may give you an idea of what to do:

Sub shpe()
Dim shp As Integer
Dim c As Long
shp = Sheet1.Shapes.Count
c = Sheet1.Shapes(2).BottomRightCell.Row
Sheet1.HPageBreaks.Add before:=Rows(c + 1)
End Sub

I ran out of time to work on this for you, will check back later,
Mike F

 

[Mike Fogleman]

Try this out:

Sub ShapeBreak8()
Dim shpcnt As Long
Dim shprow As Long, i As Long

ActiveSheet.PageSetup.PrintArea = ""
shpcnt = Sheet1.Shapes.Count

For i = 1 To shpcnt
If i Mod 8 = 0 Then
shprow = Sheet1.Shapes(i).BottomRightCell.Row
Sheet1.HPageBreaks.Add before:=Rows(shprow + 1)
End If
Next i
End Sub

Mike F

 

[NickHK]

Better add code to delete all HPageBreaks first
with ActiveSheet
'Speed up display
.displaypagebreaks=false
.ResetAllPageBreaks

Also
For i = 1 To shpcnt
If i Mod 8 = 0 Then
can be written
For i = 1 To shpcnt step 8

'Reset to see page breaks
ActiveSheet.displaypagebreaks=true

NickHK