Change Pivot Table Data Source based on Dynamic Range

**hamidxa** · 09-16-2014, 01:45 PM

I have a workbook which fires off a series of macros, but I just realized that my Pivot Tables don't update properly as the source worksheet/tab is constantly changing in terms of number of rows that need to be referenced.

So basically, the source sheet named: "S_Detail" can vary in terms of rows (can go up or down), but will always have the same number of columns
The sheet with the Pivot Tables, "PT_Count" contains 3 pivot tables that all need to reference the entirety of the "S_Detail" sheet.

Rather than clicking on each of the 3 pivot tables in "PT_Count", going to Options > Change Data Source > and changing my range (with respect to number of rows in the "S_Detail" sheet), how would I do this through VBA to capture an ever-moving dynamic range in "S_Detail"?

By the way, the 3 pivot tables in "PT_Count" are:
PivotTable3
PivotTable5
PivotTable6

I've tried a few different methods, but I'm coming up with errors.

Any help would be greatly appreciated!

**Ron Coderre** · 09-16-2014, 02:07 PM

I took a quick look at your workbook and immediately saw something you can do to avoid dynamic named ranges and changes to the pivot table data sources.

Change your source data, on the S_Detail sheet, to an Excel Table.
...Tables are one of the better features of Excel. They automatically expand and contract to accommodate the available data AND referencing them creates Structured Table References (which are descriptive, so you quickly know which data is being referenced)

• Select your source data on the S_Detail sheet...range A2 through the bottom right corner of your data
• Home.Format_as_table...follow the prompts (the default name will be Table1, in your workbook)
• Change the pivot table data sources
...Select a pivot table
...PivotTableTools...Options...Change_Data_Source
...Navigate to the source data and select all of it.
......Excel will use this reference: Table1[#All]
...Click: OK

That's it!
Now, whenever the dimensions of the Excel Table changes, all you have to do is refresh the pivot table(s). Since they reference the Table...all of the table data will be included.

Is that something you can work with?

**hamidxa** · 09-16-2014, 04:46 PM

Ron,

That is a very novel solution!
It does work as suggested, and I am certainly grateful for you posting that approach.

However, I would be curious to know if there is still a different way of approaching this using a VBA based macro to account for an ever changing dynamic range on my source data sheet?