getting information about a pivot table

[Tim]

Hi,

i have several pivot tables; each table shows the same sort of data: eg,
'TVC' which show 'Count of TVC', 'Min of TVC', 'Max of TVC', 'Average of
TVC'. (to explain, the next table would show something like 'Count of
Spores', 'Min of Spores', 'Max of Spores', 'Average of Spores'). i want to
use VBA to re-name these automatically generated names, so: -

'Count of TVC' becomes 'TVC (Total Samples)'
'Min of TVC' becomes 'TVC (Minimum)'
'Max of TVC' becomes 'TVC (Maximum)'
'Average of TVC' becomes 'TVC (Average)'

i want to develop it in vba because it is the sort of thing that i do alot,
but i cannot get the syntax correct to work on the current pivot table, or
to process through each pivot field.

i thought something like this (below) would help, but i can't get any
variation of it to work: -

---
MyValue = InputBox("What Test?")

ActiveSheet.PivotTables().PivotFields(1).Caption = MyValue & " (Samples
Tested)"
ActiveSheet.PivotTables().PivotFields(2).Caption = MyValue & " (Minimum)"
ActiveSheet.PivotTables().PivotFields(3).Caption = MyValue & " (Maximum)"
ActiveSheet.PivotTables().PivotFields(4).Caption = MyValue & " (Average)"

---

if anyone can understand this, please help!

Thanks,

Tim

 

[Guest]

HI Tim
Try this:
Sub CHGPVTNAMES()
Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables
pt.DataFields(1).Caption = "YOUR TEXT "
Next pt
End Sub

Mike

 

[Tim]

Mike, thanks for that... could you or someone else extend it slightly
further to tell me how to do the same thing on just the pivot table in which
the active cell currently resides (ie, just change that table, not all
tables)... i know this slightly contradicts what i said before, but both
scenarios apply.

thanks,

Tim