Pivot Shortcuts (most likely VBA)

[cpadilla0024]

Hi,

I have workbooks that have multiple pivot tables within them. One struggle that I am having (that can be manually time consuming) is changing the data source on multiple tables at once and alphabetizing all the fields on all all tables at once. So, one of these is needed because we do interim and model reporting and our sources are a little mobile. And the other is for my contacts that sometimes have to filter off my tables.

If there is something I am missing without VBA, I would prefer that but I also welcome VBA solutions. Thanks for your help!

 

[bluesky63]

Hi, when you mention change data source means manually the range ? actually you do not need to do that if all your data source is a table, so it will be dynamic, meaning no matter how you change, update the source table (add rows, column, change formula etc), the Pivot table will just need to press refresh (or can set automatic refresh)

I do not think you require VBA, in fact Power Query can resolve your current Pivot Table way of data transform, just need to develop a simple query (UI base) in the editor once

you can upload a sample table and I can show you both Pivot table (with auto update) and Power Query way

 

[cpadilla0024]

Hi Bluesky,

Not the range. I already have my sources in tables that dynamically updates for my pivots. I am having issues referencing a completely new data source or table that may be in a completely different workbook. It is a multiple tables at once sort of deal. I would attach my workbook but it is way too big.

Also, for alphabetizing my pivot fields, I have a macro that has been handling one at a time but I would like to get one that can do the whole workbook. See below:

Sub AutoSortAZAllFields()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
   
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    For Each pf In pt.RowFields
      pf.AutoSort xlAscending, pf.SourceName
    Next pf
    For Each pf In pt.ColumnFields
      pf.AutoSort xlAscending, pf.SourceName
    Next pf
  Next pt
Next ws

End Sub