Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Pivot Table VBA Questions
(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Pivot Table VBA Questions
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. -- Regards, Anant "green biro" wrote: (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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Pivot Table VBA Questions
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 "Anant Basant" wrote in message ... 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. -- Regards, Anant "green biro" wrote: (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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Questions | Excel Discussion (Misc queries) | |||
Pivot Table Questions | Excel Discussion (Misc queries) | |||
Pivot Table Questions | Excel Discussion (Misc queries) | |||
Pivot Table Questions | Excel Discussion (Misc queries) | |||
Pivot Table questions | Excel Discussion (Misc queries) |