Changing source of all pivot tables in a workbook

[EliseFreedman]

Hi All

For the past few months I have been building a dashboard at work using excel. Until now the dashboard consisted of a couple of sheets of graphs/charts etc which were based on pivot tables within the workbook. The underlying data was also on the workbook. All was working fine until I began to expand the functionality of the dashboard and I started getting errors staying that excel didn't have enough resources etc. I have now had to go back to the drawing board. It appears that the only way to stop the resource errors is to remove the worksheets containing the underlying data into a new linked workbook (I suspect the errors were because the data was getting deleted and reentered weekly). Once ive removed the worksheets containing the data, is there an easy way to update all the pivot tables in the dashboard to use the new location of the underlying data rather than the old location.

 

[strongm]

Loop through the PivotCaches collection and change the SourceData for each appropriately, e.g.:

[blue]Dim myCache As PivotCache
For Each myCache In ActiveWorkbook.PivotCaches
    myCache.SourceData = "Sheet1!R1C1:R4C1"
    myCache.Refresh
Next[/blue]

 

[SkipVought]

I suspect the errors were because the data was getting deleted and reentered weekly

Try making the source data table a Structured Table. A Structured Table has a Name and has Named Ranges. If your PTs reference the ST Name doesn't matter that data is deleted and reentered.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

>Once ive removed the worksheets

Skip, I'd presume that removing the worksheets would remove the structured tables ...

 

[SkipVought]

I was surmising that it might not be necessary to remove the source data tables if they were STs.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

Skipvought

My data is already in a table

 

[SkipVought]

A Structured Table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EliseFreedman]

Yes

 

[SkipVought]

Okay. Surprising.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein