How to create a pivot field per table header in VBA?-CodePudding

For my pivot table, i want to create a data field for each selected headers (e.g. "A","B","C",etc) from my source table. Instead of writing a separate code for each header, i though of creating a loop through the headers as i have over 10 headers.

My attempted loop code:

Dim h As Variant
Dim p As Long
Dim hdrs As Variant 'table headers of interest
hdrs = Array("A","B","C")

For Each h In hdrs
    With PSheet.PivotTables(PTName).PivotFields(hdrs)
    .Orientation = xlDataField
    .Function = xlAverage
    .NumberFormat = "0.00"
    .Position = p   1
    End With
Next h

Variables of my code:

PSheet: worksheet holding my pivot table (Worksheet variable)
PTName: name of pivot table (string variable)

But nothing happens with this code

CodePudding user response：

This worked:

Dim hdrs As Variant
Dim i As Long
hdrs = Array("A", "B", "C")

For Each Item In hdrs
    PSheet.PivotTables(PTName).PivotFields (Item)
    With PSheet.PivotTables(PTName).PivotFields(Item)
    .Orientation = xlDataField
    .Function = xlAverage
    .NumberFormat = "0.00"
    .Position = i   1
    End With
Next Item