Thread: vba - formatting multiple pivot charts?

Hello All,

So found out how to format pivot tables using a for each...in .... loop, but need to do the same for pivot charts, and one bit of code is off.

Here is the successful code for pivot tables

Code:

Dim Worksheet As Worksheet
Set Worksheet = ThisWorkbook.Worksheets("Tables")
For Each PivotTable In Worksheet.PivotTables
    With PivotTable
        .RefreshTable
    End With
Next PivotTable

and here is the unsuccessful code for the pivot charts

Code:

Dim Worksheet As Worksheet
Set Worksheet = ThisWorkbook.Worksheets("Charts")
For Each ChartObject In Worksheet.ChartObjects
    For Each Chart In ChartObject.Charts
    With Chart
        .ChartType = xlLine
        .Axes(xlCategory).TicklLabels.Font.Size = 10
        .Axes(xlValue).TicklLabels.Font.Size = 10
    End With
    Next Chart
Next ChartObject

Code breaks on this line

Code:

      For Each Chart In ChartObject.Charts

With the phrase

Object doesn't support this property or method

sigh. so close.


EDIT: see follow-up question below

Avoid using variables names that are already being used as object names.

a chartobject does not have multiple charts.

Code:

Dim shtTemp As Worksheet
Dim objCht as chartobject

Set shtTemp = ThisWorkbook.Worksheets("Charts")
For Each objCht In shtTemp.ChartObjects
    
    With objCht.Chart
        .ChartType = xlLine
        .Axes(xlCategory).TicklLabels.Font.Size = 10
        .Axes(xlValue).TicklLabels.Font.Size = 10
    End With

Next

You can use the macro recorder to get most of the code.

You will also need to use the SeriesCollection and Datalabels collections.