Two Pivot Table VBA Questions

[green biro]

(1) How do I replace a datafield in each pivot table in my workbook
programatically?

I want to aggregate a different field. I know that addfields sort of does
this but it also gets rid of my col, row and pagefields. And sometimes I
have the same data field in twice (once formatted as number and once as
percentage). In these cases I would want to replace it twice. Then

(2) How can I just add a page field to a pivot table?

Addfields has an appendfield argument but that only applies to a pivot chart
rather than a pivot table. I'm thinking that I could somehow extract an
array of each type of field for each pivot table and feed it back in to the
addfields method but I'm not entirely sure how to do that and it would seem
rather long winded.

Thanks in advance for any help.

 

[Guest]

Hello,

I am assuming that you have multiple similar pivottables in
multiple sheets in the workbook and the column
being replaced is the same

Sub ReplaceDataColumnInPivotTable()
Dim shtCount As Integer
Dim i As Integer
Dim pt As PivotTable

shtCount = ActiveWorkbook.Sheets.Count
For i = 1 To shtCount
If TypeName(Sheets(i)) = "Worksheet" Then
For Each pt In Sheets(i).PivotTables
pt.PivotFields("FieldToReplace").Orientation = xlHidden
pt.PivotFields("FieldToReplaceWith").Orientation = xlDataField
Next
End If
Next i
End Sub

For 2nd question:
The code above could be tweaked a little as per your requirement for
pagefield also. Orientation can be set to xlPageField.

 

[green biro]

Thank you.

I modified the code as suggested to add my page fields, which in the end was
all I needed.

I'd have to experiment a bit with changing the data field because I had the
same data field in twice in my pivot table - once formatted as number and
once as percentage of row. I think that Excel appends a "2" to the field
name when it's added for the second time but it's unclear how I would
replace it both times with the new field. Luckily I didn't need it in the
end but it's one to ponder over...

Thanks again for help.

GB