Automatic Pagebreaks

[Roderick_E]

On Excel 2007-2013, if I simply format a new sheet as landscape, narrow margins; there are automatic pagebreaks. They appear to be every 34 rows or positionally the first break appears at 513.75. You'd think you could just increase in increments of 513.75 to the next break but I'm doing something wrong. I'm trying to insert charts/objects with the exact same height and widths at the very top of each page break, but somehow they keep drifting down into the next pagebreak after the 4th or 5th chart.

SAMPLE CODE:

shtop = 0 'start at very top of page
For Each Shape In activesheet.Shapes
Shape.Height = 495
Shape.Width = 665
Shape.Top = shtop
shtop = shtop + 513.75
next

Any ideas how to make the drift stop? I would simply check where the next automatic pagebreak is, but it doesn't seem accessible. Thanks

 

[Roderick_E]

Well, I added a bit of compensating code...that seems to bring it back in line.

'resize charts
chartcnt2 = 0
For Each Shape In activesheet.Shapes
Shape.Height = 495
Shape.Width = 665
Shape.Top = shtop
shtop = shtop + 513.75
'account for drifting top position
If chartcnt2 > 2 Then
shtop = (shtop - 5)
End If
If chartcnt2 > 8 Then
shtop = (shtop + 2)
End If
chartcnt2 = chartcnt2 + 1
Next

You can test the Tool by downloading it from rodericke.com/xlsuper Then use the "Chart Copy" feature after LAUNCH. (will make sense when you see the UI)