Change Pivot connections with vba

RSBaidoo

New Member
Joined
Jun 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi colleagues,
Source connection to my pivots are all queried data but anytime i change the one of the pivot table connection the other pivots connection does not change.

I tried recording a macro whilst changing the connection of the pivot table but this looks tedious since i have to change the pivot table names manually in the codes.

Please how can I use vba to change the connection to the pivots once so that all the pivots will is linked to one connection and refreshes anytime one first pivot is refreshed.

Thank you
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
When you say queried data what exactly do you mean?

Is it possible to load the query into PowerPivot and use that as the Pivot data source?

The other option would be to maybe run a macro on pivot change for a pivot that will then loop through all others. Trick there being not retriggering that macro into an endless loop.

I'd use the first option if possible personally.
 
Solution

RSBaidoo

New Member
Joined
Jun 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for the insight

I tried the second option which is running a macro on the pivot that will loop through the others.
And it gives me run-time error saying "Object variable of With block variable not set"

This is the coding I did...

1623409882162.png


Grateful for your help!
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
I'm not back in front of a PC for a few days so I'm not sure on the exact syntax needed here.

What's the result when you record a macro and change the query?
 

RSBaidoo

New Member
Joined
Jun 10, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
After recording and changing the query it worked perfectly

But having a run time error after inculcating the loop

This is the macro i recorded

1623667651184.png
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
What does it all look like together and where abouts is it giving the error, and which error.

Pasting in the code would help instead of a screenshot to try and replicate the issue. Cheers
 

Forum statistics

Threads
1,147,744
Messages
5,742,948
Members
423,766
Latest member
dafydd_jwc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top