Auto Refresh PivotTables & Queries - without VBA!

Hi, Mynda.. Thank you for your valuable video. I have one question. Once applied VBA code cannot apply UNDO and REDO. Any solution for this? Please

Simply Supperb collated every doubt in one video with practical example too ❤
Lots of love from India 🇮🇳

i am using one of this option for one dashbaord but when i am giving this one to another person then his system it is not working, showing odbc excel driver login failed, how can resolved this and use on different system?

how can I refresh the excel pivot if the file is closed

Hello, How to refresh using office scripts.

Hello Mynda! I keep facing one problem, whenever I change a cell value in my data table. the query table kept the old row with old value and add a new row with the new value. I don't know if there is an option to avoid this problem. thank you in advance.

when i make the table dynamic and go to power query and i press close & load to ( only create connection) dont use why ?

I have a power query that reads in data from a database and is loaded into the excel sheet. From here I then add it to the data model and do some further processing in power pivot. Finally this is then loaded into a pivot tables and pivot charts. I have found that I need to click Refresh All twice to update the pivot tables and charts. In order to fix this should I not have the power queries load into the excel sheet and go straight to power pivot?

Great video!!

Amazing vba code for instant updating pivot tables according to source. Thank you

Seems like this video over-promises, in that the auto-refresh options are not immediate. Only the last segment gives an immediate solution and that is 'with VBA!'.

Hi, does this not work if you have different sources linked to diff pivot tables....will this only work on a work book with the one source? Thanks

Question:

Firstly thanks for the video so I have a spreadsheet which has multiple projects and each project has its own tab.

I have a summary sheet which I use indirect look ups to get the data from each sheet, this data is wide so I unpivot with power query straight to a pivot and have a chart of the back of it.

I am trying to find a VBA code that every time the numbers in the summary sheet change it updates my pivot table automatically.

Issue that I am running into is that I update each individual sheet which feeds into the summary sheet but technically nothing changes in the cells in the summary sheet as the formula stays the same (even thought the numbers are different)

Excel isn’t detecting a change as the formula is still the same so how would I go about creating a code for this scenario

That is very helpful. i fetch data from a OLAP cube !!! I want to wait until a power query finished refreshes!!!
Often facing the issue that file's refresh is stopped in between. Please povide a suggestion

Using in my workbook combination of PowerQuery + PivotTabe + function GETPIVOTDATA was definetly a big mistake. Thanks for the explenation.

It would be really appreciated if you could help me out. It seems that I am only allowed to use power query to change data column type/format if I do not want to import the data to table. I am attempting to change a data column to a currency type. The default conversion result is an amount with thousand separators and two decimals, and without currency symbol. I need to change it to Japanese yen. Can I add a yen currency mark and remove the decimals?

Many thanks for this insightful video. Given that all the settings have been configured in a normal mode, does Auto Refresh PivotTables also work in a read only excel?

can the query be auto refresh when file is closed?

I can't load queries and connections. It's greyed out all the time. I'm using excel on Macbook M!

Your tutorials have changed my life! THANK YOU! Especially the project management dashboard! Please help on the VBA - I used the last method in this tutorial. To auto refresh the pivot table. It worked once, and then kept giving me a Run Time Error "1004" thereafter. Id be so grateful for some guidance to resolve this.

This is a helpful video, but it speaks to the sheer chaos that Excel has become over the past twenty years. Would a simple radio button saying "auto refresh" in the modular dialogues/wizards where you create the pivot table be so difficult for Microsoft to implement? Is refreshing pivot tables REALLY so resource-intensive that stonewalling people from updating them automatically is the most streamlined possible way of doing this? How is it possible for Google Sheets to have seamlessly accomplished this in a browser almost a decade ago? Because of data security agreements at my company, I cannot use MS 365 and am stuck on the 2016 desktop version of Excel without the ability to use the VBA editor. I would do anything to use VBA or get the new array formulas like FILTER(), SPLIT(), and UNIQUE(). I can't stand faffing around with the data model, these ridiculous dialogue boxes, and hitting Alt>A>R>A every 11 seconds like a trained rat. ;_____; Also big shoutout to everyone who has been gaslit in the MS product forums for asking for basic functionality like this.

Hi Mandy, Your video are always amazing! I have a question, could be that using pivot from data table from power query we lose some functions in the pivot?
I'm tryin to work with formatting table and if I try to highlight in red a column i don't have this option, I can do it only forthe single cell. even the function calculated filed doesn't semme work as normal pivot...

Hi Mynda! Great features! Many thanks. Do you know if the Query Properties set to refresh every 15 min will run only with the File opened? Or will it also do the job when the file is closed?

Very informative. Thanks.

Thanks

Can the queries be auto refreshed when source data changes?

What about XL files in OneDrive? Can they update queries without being physically open in excel?

Thanks for sharing your knowledge....amazing

Q. Is there a way to refresh pivot tables automatically when we refreshed the query connection.

This was useful, thanks a lot!

I have a question that will be a huge help if solved! Can you update multiple sheets of pivot tables at once? In my example there are about 50 clients and multiple accounts each and a $ amount for each account. I need to change the data dump every month, so is there a way to change data sources ( might have more rows/ accounts). Additionally every sheet filters by client name and it sums the totals of each account of the client. Right now I have to go to each sheet (40+) and click on the data source to change it.

May I please ask if auto refreshing every 60mins works even when the file is closed? My query is linked to the folder where new files are added. Your advise is greatly appreciated! Thank you!

Thank you for the video. Extremely easy to follow and useful. Is there a VBA that allows the same "auto refresh" when the sheets are protected? In my scenario. Both the Source Data Table and PivotTable(s) reside in different worksheets in the same workbook. PivotTables are linked to charts in other protected sheets in the same workbook. The entire workbook is protected but specific cells in the other worksheets are unprotected to allow other users to enter data. I've found some answers online that indicate it is possible but the VBA does not fit our scenario. I'm hoping you can help.

I need tor efresh a table when the infirmation in other sheet is updated instanly without hitting refresh HELPPPPPPPPPPPPPP!!!

Pleasew which excel version are you using?

Thank you!! I have been wrestling with the "PQ to Table to PT" scenario and wondering why the refreshes are not dependable or require multiple refreshes. First time to hear this information. 👍

Hello,
I saw your many videos and learned lot of them,
Actually I'm looking for dropdown options, could you please let me know how can I create multiple dropdown in single cell.
"like category and sub category in single cell"

Mynda, thank you for the great explanation.
Even though the automatic update feature with VBA is excellent, it should not be forgotten that the operations made in VBA cannot be undone. For example, after a change in the source table, the automatic update (with VBA) in the pivot table causes a previously entered data to not be recalled, so the best and correct solution is "right click, update" ;))

Hi, please, is it possible? I want to design a warehouse program that has 6 store branches Belize

Thanks Mynda for this very useful video! You always bring something innovative to help us solve real world problems!

Love your videos it helps a lot.

Great video as usual Mynda. Thank you for the tips. Unrelated to this I have a question about Power Query. Can you please make a video about lookup with wildcards in Power Query. Should I use the fuzzy matching or there is another way?

Extremely useful tips!

Is there any function to know second lowest value in row, but value are in alternate column

Hi Mynda, Thanks for the video. One question, or two. When trying to make a connection only for the PQ, the option to do that is greyed out. Also on inserting a pivot table, I do not have the drop down options as you showed. I am using 365 and just wondered if you any ideas? Thanks for your help.

Thank you Mynda its really helpful but in the code you write for loop inside another for loop, my question is this loop complete as i am working in the excel file but as i know that infinite loop is not allowed and cause problems. But thanks for your effort anyway.

Hi, Mynda. Good topic. PT refresh is so frustrating. I almost always use VBA. Mostly b/c my workbooks tend to be “Excel apps” with lots of worksheets, tables, PQs, DAs, PTs, & buttons. “RefreshAll” usually is non-starter b/c we only need to refresh what’s shown on selected worksheet & that worksheet’s hidden helper worksheet (if it exists). So, a button with an assigned macro that refreshes only the PQs & PTs that need to be refreshed does the trick. (I love DAs & use them when appropriate instead of PTs.) All of that said, I like your VBA approach. Very clever. I’ll will be able to use it. Thank you.

Off topic comment/question: Would you describe how you use the worksheet & ThisWorkbook code modules? I have used them only for their event procedures. In Your example, I would have used the worksheet module to house the Worksheet_Deactivate event, but I would have put the RefreshAll sub in a standard code module. My projects tend to have lots of code modules. I wonder if your approach would improve my code storage. What are your rules for using the worksheet & workbook modules v. standard code modules?

Hi Mynda. Great tips for updating Pivot Tables and Queries! Thanks for sharing :)) Thumbs up!!

This is great, thank you!!

I'm using the method starting at 7 min in (using classic pivot tables) and I notice that if I copy a cell in the source data tab, when I go to paste it into another tab, the paste function is not available, as if the copied cell has been cleared from memory. Is there a way around this?