Loop through pages

[Roderick_E]

I have a worksheet that I'm currently viewing as page break.
I want to loop through all print-area pages and on each page determine how many chart objects are on each page.
Any suggestions? -- VBA obviously. Thanks

 

[John_w]

Try this code as a starting point.

Public Sub Count_Charts_On_Each_Page()

    Dim page As Long
    Dim chartObj As ChartObject
    Dim HPageBreakStartRow As Long
    Dim numChartsOnPage As Long
    
    HPageBreakStartRow = 1
    With ActiveWorkbook.ActiveSheet
        For page = 1 To .HPageBreaks.Count
            numChartsOnPage = 0
            'Debug.Print page, HpageBreakStartRow, .HPageBreaks(page).Location.Row
            For Each chartObj In .ChartObjects
                'Debug.Print chartObj.BottomRightCell.Address
                If chartObj.TopLeftCell.Row >= HPageBreakStartRow And chartObj.BottomRightCell.Row < .HPageBreaks(page).Location.Row Then
                    numChartsOnPage = numChartsOnPage + 1
                    'Debug.Print chartObj.Name & " is on page " & page
                End If
            Next
            Debug.Print numChartsOnPage & " charts on page " & page
            HPageBreakStartRow = .HPageBreaks(page).Location.Row
        Next
    End With

End Sub

 

[Roderick_E]

Wow, this is great thank for the feedback and code. It doesn't get the last page since it is counting page breaks.
Maybe thinking about reworking with

With ActiveWorkbook.ActiveSheet
For page = 1 To .Pagesetup.pages.count

 

[Roderick_E]

Hmmm, maybe I'm going about this wrong. What I'm trying to accomplish is automatically align charts in a pagebreak section. If 1 chart then take up entire pagebreak section. If 2 charts, place then over each other horizontally taking up entire pagebreak section. If 3 charts, 2 on top next to each other and 3rd on bottom. If 4 then 4 evenly spaced squares within the pagebreak section.

Left is easy enough to determine if there is only 1 chart but the rest is very dynamic. Where the top and bottom are and such.