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.
Do you actually need the table on a worksheet?
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.
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)
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?
I amended the code you had in the Worksheet_Deactivate event to simply refresh the pivot.
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!
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.
