Captions in Pivot Tables (VBA)

[JayCheezey]

So I'm new to coding and I was just wondering...


Basically, I''ve created a code (for pivot tables) that auto refreshes and inputs the new field into the Values table relative to a cell that has a value (201428).


ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Pivot").Range("CC1").Value), "Sum of 201428", xlSum

I've managed to get excel to add the field that has the value of CC1 to the Values category. That was the hard part... The easy part I can't figure out is how to display this value in the caption. Above, you see that the value 201428 is displayed. But I want the sum of ... to be relative to a cell.

I tried to do this:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Pivot").Range("CC1").Value), Sheets("Pivot").Range("CC1").Value, xlSum

But it didn't work...

I know there's a simple solution to this. Just don't know what haha...

Thanks for the help in advance!

 

[Gavin T]

The caption and name of a pivot field cannot be the same. Try this to see how it would work:

ActiveSheet.PivotTables("PivotTable1").AddDataField _
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Pivot").Range("CC1").Value), _
"Sum of" & Sheets("Pivot").Range("CC1").Value, xlSum

 

[JayCheezey]

Thanks a lot Gavin! Works perfectly!

The caption and name of a pivot field cannot be the same. Try this to see how it would work:

ActiveSheet.PivotTables("PivotTable1").AddDataField _
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Pivot").Range("CC1").Value), _
"Sum of" & Sheets("Pivot").Range("CC1").Value, xlSum

 

[Gavin T]

You're welcome.