Log In
Register
Home
Trusted Members
February 13, 2021
Offline
I'm at my wits end! No matter what I do I CANNOT get my pivot table to refresh without running the code twice. I know the issue, I just can't figure out how to get around the issue. I have a table that is my input table, I then use PQ to clean up the data and use that table to feed through to a pivot table. When I update I need to update the PQ table first in order to refresh my pivot but I can't get it to work without running the macro twice, HELP PLEASE! I will love you forever! Workbook attached, and I am using Excel 2013.
See Answer
Trusted Members
Moderators
November 1, 2018
Online
Do you actually need the table on a worksheet?
Trusted Members
February 13, 2021
Offline
I don't know a whole lot about PQ, this is the first project I've tried it on. As long as it can be used to feed the pivot table, no the table result from PQ doesn't need to be seen or used by the end user.
Trusted Members
Moderators
November 1, 2018
Online
Make it a connection only query, then use that as the source (choose external data) for the pivot table.
Something like the attached (you have to recreate the pivot unfortunately)
Answers Post
Trusted Members
February 13, 2021
Offline
I made the changes you suggested - thank you so much! I was debating whether to hide the sheet in the end! Nifty trick! But my pivot table is still not loading unless I manually refresh the query, I'm not seeing that you added any code to the worksheet change event so I assume it's a refresh setting in the query but I can't find it? What am I missing?
Trusted Members
Moderators
November 1, 2018
Online
I amended the code you had in the Worksheet_Deactivate event to simply refresh the pivot.
Trusted Members
February 13, 2021
Offline
Yes I saw that, I made that change too. It's not refreshing unless I do a manual refresh of query on my original worksheet. I'm not sure if there's a setting on automatically refreshing the query that I missed in your attachment - because yours works perfectly! I'll attach my worksheet with the changes I've made. I appreciate your help!
Trusted Members
February 13, 2021
Offline
I got it, thank you so much! I had the data added to the data model, once I removed that it is working now. Because I was an American teenager in the 90's and I'm feeling nostalgic, you are da bomb dot com! I appreciate all of you help.
1 Guest(s)
