Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting information about a pivot table
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting information about a pivot table
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting information about a pivot table
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 "MIKE215" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Information for Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table cutting information of a part of the information | Excel Discussion (Misc queries) | |||
Pivot Table moving information | Excel Discussion (Misc queries) | |||
Using the information from a pivot table | Excel Worksheet Functions | |||
pivot table maybe?? combining information | Excel Discussion (Misc queries) |