New Data Field Into Multiple Pivots

[no1gdog]

Hello,
I have about 26 pivots which are reports by 26 Areas. There is a Data tab that is refreshed monthly and a new column of data for the current month is populated. The challenge is I have to go into each pivot after I refresh all and add the new column of data to every single pivot (also when I add the new column of data it Counts rather than Sums so I need to go into Field Value Settings to select Sum).

Anyway to automate this? I am thinking if there's a way to select the desired month (003.2018 for example for March 2018) that each pivot can automatically add that new field into the Values area of the Pivot rather than going into each one and dragging the new month's Field into Values then Select Field Value Settings and change to Sum....



Thanks!
Gary

 

[Jerry Sullivan]

Hi Gary,

If you use a dynamic range as the reference for the PivotTable data source, that will address the issue of adding the fields to the PivotCache and possibly eliminate the need to change the value settings to Sum. You can either of these methods to create a dynamic range for you data source.

1. Use and Excel Table (ListObject)
2. Create a DNR (Dynamic Named Range)
3. Use VBA to automatically redefine a named range when changes are made.

I'd suggest the first option, Excel Table as the simplest, provided that your source data is in the same workbook as your PivotTables.

Regarding adding the new month's Field into the Values area, that could be automated with a VBA macro.

If you want an alternative that doesn't require VBA, you could restructure (unpivot) your dataset to have a field for the Month, then add that Month field to the Column field area of the Pivot. As new months are added to the data set, they would show up as new columns of Items (not Fields) in your PivotTable.

 

[no1gdog]

Thanks Jerry, what do you mean about the issue with adding fields to the PivotCache?

 

[Jerry Sullivan]

The PivotCache is just the source data read into memory when the PivotTables are refreshed. If your source data is the range Data!A1:E100, and you add a new month's data to Data!F1:F100, that extra column of data needs to get added to the PivotCache data that is read by the PivotTables.

You can do that manually by:
1. Changing the source data in each PivotTable from Data!A1:E100 to Data!A1:F100 (a time-consuming process).
2. Inserting a new column between Data!A:E then adding your new month's data to that column.

You can do that automatically by using a dynamic range source using one of the 3 methods I noted in post #2 .

 

[no1gdog]

Got it, that's what I thought you meant, thank you. This data set has H:AQ selected within that range when the new data populates it is not selected within the pivot since we are using SAP Analysis for Office, creating a Table won't work. So when the new month's data comes in, for example this current month's fiscal month/date is 008.2018 so it has to be selected and placed in Values section since the Columns are sum by month. Is there anyway you can write a VBA code to do this as well as change from Count to Sum?

I have to do this on 26 tabs so it's time consuming. On the macro there has to be a way for me to tell it what data field name to select and add to Values field in the pivot, maybe a prompt comes up. (Let's say the Pivot is on Sheet 1 and the data is on a tab called Hours) I could embed this macro on each sheet and edit the VBA code to point at that tabs Pivot(s) that I want it to change or maybe there's a way to have one code that is applicable to all (I was thinking I can add the names of all the tabs and pivot table names and run the macro one time for the whole sheet. I just need to generate the first VBA code to start editing. Can you help? I am ok with editing just not generating the first code...