Bruce,
If you want the bottom of the page according to Excel/page set up/printer
margins etc the you want automatic page breaks.
If you are inserting page breaks, then you are deciding where the end of the
page is and you need to decide where "Add Befo=ActiveCell" should be
located.

In relation to pagebreaks there is also the "ExecuteExcel4Macro()" function
(with the correct argument, that I've forgotten at the moment) that return
page break info and seems more reliable than the newer collection.

However, as you page size is already set, do you mean to place/size your
charts correctly instead ?

NickHK

"Bruce" wrote in message
...
I have a worksheet in which I add a number of charts via vba. The number
of
charts added changes (eg may be 6 or 20 or more) depending on my data. The
arragement on the page is 2 x n where there are 2 across the page that fit
the print area.

How do I find the bottom of each page so that I can insert the required
page
breaks into the worksheet with the following or something else;

ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell

Bruce

Hi Nick,

I have setup the printer margins and in terms of width the charts fit.
However vertically the charts overlap pages in their current positioning. I'm
not sure as you say, how to determine where the bottom of the page is so that
those charts can be positioned correctly.

This is the code I am using to add size and position the charts. I guess its
somewhere around the .top but any help is appreciated.

Bruce

Private Sub lineupCharts()
Dim MyWidth As Single, MyHeight As Single
Dim NumWide As Long
Dim iChtIx As Long, iChtCt As Long

arrChart = Array("Chart_Int", "Chart_Short", "Chart_Long")

MyWidth = 240
MyHeight = 145
NumWide = 2
For Each a In arrChart
Sheets(a).Activate
iChtCt = ActiveSheet.ChartObjects.Count
For iChtIx = 1 To iChtCt


With ActiveSheet.ChartObjects(iChtIx)
.Width = MyWidth
.Height = MyHeight
.Left = ((iChtIx - 1) Mod NumWide) * MyWidth
.Top = Int((iChtIx - 1) / NumWide) * MyHeight
End With
Next
Next a

End Sub




"NickHK" wrote:

Bruce,
If you want the bottom of the page according to Excel/page set up/printer
margins etc the you want automatic page breaks.
If you are inserting page breaks, then you are deciding where the end of the
page is and you need to decide where "Add Befo=ActiveCell" should be
located.

In relation to pagebreaks there is also the "ExecuteExcel4Macro()" function
(with the correct argument, that I've forgotten at the moment) that return
page break info and seems more reliable than the newer collection.

However, as you page size is already set, do you mean to place/size your
charts correctly instead ?

NickHK

"Bruce" wrote in message
...
I have a worksheet in which I add a number of charts via vba. The number
of
charts added changes (eg may be 6 or 20 or more) depending on my data. The
arragement on the page is 2 x n where there are 2 across the page that fit
the print area.

How do I find the bottom of each page so that I can insert the required
page
breaks into the worksheet with the following or something else;

ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell

Bruce

Bruce,
"ActiveWindow.SelectedSheets.HPageBreaks(1).Locati on.Address" will give you
address of the first break.
You can also set the Location to a range to insert a break.

Do that help ?

NickHK

"Bruce" wrote in message
...
Hi Nick,

I have setup the printer margins and in terms of width the charts fit.
However vertically the charts overlap pages in their current positioning.
I'm
not sure as you say, how to determine where the bottom of the page is so
that
those charts can be positioned correctly.

This is the code I am using to add size and position the charts. I guess
its
somewhere around the .top but any help is appreciated.

Bruce

Private Sub lineupCharts()
Dim MyWidth As Single, MyHeight As Single
Dim NumWide As Long
Dim iChtIx As Long, iChtCt As Long

arrChart = Array("Chart_Int", "Chart_Short", "Chart_Long")

MyWidth = 240
MyHeight = 145
NumWide = 2
For Each a In arrChart
Sheets(a).Activate
iChtCt = ActiveSheet.ChartObjects.Count
For iChtIx = 1 To iChtCt


With ActiveSheet.ChartObjects(iChtIx)
.Width = MyWidth
.Height = MyHeight
.Left = ((iChtIx - 1) Mod NumWide) * MyWidth
.Top = Int((iChtIx - 1) / NumWide) * MyHeight
End With
Next
Next a

End Sub




"NickHK" wrote:

Bruce,
If you want the bottom of the page according to Excel/page set
up/printer
margins etc the you want automatic page breaks.
If you are inserting page breaks, then you are deciding where the end of
the
page is and you need to decide where "Add Befo=ActiveCell" should be
located.

In relation to pagebreaks there is also the "ExecuteExcel4Macro()"
function
(with the correct argument, that I've forgotten at the moment) that
return
page break info and seems more reliable than the newer collection.

However, as you page size is already set, do you mean to place/size your
charts correctly instead ?

NickHK

"Bruce" wrote in message
...
I have a worksheet in which I add a number of charts via vba. The
number
of
charts added changes (eg may be 6 or 20 or more) depending on my data.
The
arragement on the page is 2 x n where there are 2 across the page that
fit
the print area.

How do I find the bottom of each page so that I can insert the
required
page
breaks into the worksheet with the following or something else;

ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell

Bruce