Pivot Tables: Automatically Update When Source Data Changes

[@sstelevision6376]

If you only knew how long I've been looking for someone to explain this correctly lol. Literally took like 1 minute. Thank you!

[@rushikeshRJ9801]

This Saved my probably 2 hours of work - Very simple trick

[@scottydo2103]

Great teaching voice. You're right to the point and easy to follow along! Cheers, Scotty

[@ExcelCampus]

Thank you kindly! 😀

[@BigShlarb]

Nice!!! My first time using code and implementing a VB macro. Thanks to your video, I was able to successfully add this feature to my workbook.

[@yeahhhhh9209]

The best video and explanation on youtube.. it worked great and right away.. Thankss

[@jsingle76]

Jon, Thank you SO very much for posting this video! This was the perfect solution to a complex workbook I'm setting up to track a large amount of data in a case where people will updating the source data a lot. Can't thank you enough! :)

[@jackfirst11]

I love this vba macro. the most basic and simplest way for my need now. Thank You

[@ZacCoventry]

This is a well produced and helpful video. Thank you, Jon!

[@deanzipperer8556]

Thank you so much for this.. i learned VBA on the fly and never learned some of these little things like the drop downs in the beginning... this was an awesome thing to learn... thank you so much for taking the time to put this video together.. helped me learn something that i most certainly use on a day to day basis.

[@sulaimanal-busaidi9285]

Thank you sir for making our life easier

[@Qomri]

You're the guy Jon when it comes to VBA tips & tricks; hands down. Always right to the point - THANK YOU.

[@ExcelCampus]

I appreciate that, Qomri! 👍

[@sdvirk]

Well explained and useful too. Solves the purpose. Thanks.

[@jenniferbeaulieu1372]

Thank you Jon, you explain things so well. I had been searching for an answer on auto refreshing queries.

[@carolinademontmollin5884]

Much appreciated! Fit for a total beginner in Macros

[@mofo1625]

Thank you so much! So simple and yet and I spent so much time looking for this solution, you've brightened my day :)

[@rajeev3179]

Thanks brother love from India

[@arturgarncarz3631]

Thanks so much!!!! Game changer ;) Regards from Poland

[@funnysun3291]

Excellent lesson easier excel life..

[@olliehopnoodle4628]

Great video. In many cases you can use "Worksheet.Deactivate" event instead. This will cause the update to happen when you move from the 'Data' worksheet to another worksheet.

[@jfncho]

thank you! liked and subscribed.

[@davidtopp1972]

I am running Excel 2019 on a MacBook Pro with MacOS Big Sur. I changed my file to xlsm type. I have 10 sheets in my workbook, that is a budget and check registers for several accounts. I tried to do what is in your video, but having some doubts if I have selected the right thing, Regardless of what I select and try to change the drop down from General to Worksheet, I get a pop-up that says: Microsoft Visual Basic Varible uses an Automation type not supported in Visual Basic. Can you please help me. Thank you!

[@ExcelCampus]

Hi David, Unfortunately, that drop-down does not work on the Mac version of Excel. It is a bug that needs to be fixed. However, you should be able to copy/paste the code to the code module for the sheet. You can copy/paste the code from our accompanying blog post. Here is the link. www.excelcampus.com/vba/refresh-pivot-tables-automatically/ I hope that helps. Thanks again and have a nice day! 🙂

[@wayneedmondson1065]

Hi Jon.. nice tip on refresh.. will come in handy for certain applications. Thanks again and Thumbs up!

[@ExcelCampus]

Thank you Wayne! :-)

[@johnborg5419]

Thanks Jon. That was very Helpful since I do not know any VBA. So this is very helpful for my users. :)

[@ExcelCampus]

Awesome! Happy to hear it helps you and your users. :-)

[@ExceliAdam]

refreshing pivot tables is still a problem, and your video shows how easy it can be dealt with;)

[@ExcelCampus]

Thank you Adam! :-)

[@ankitrai7066]

hey Jon, thanks for putting this video, its really very helpful.. thanks Man

[@nowgiventobless7937]

Very good thanks

[@josephpena733]

Saved my life

[@ghstefan2897]

Works great. Thanks.

[@ExcelCampus]

You're welcome! 😀

[@Dreadnought422]

tnx, i used to delete and remake the pivot al the time. eventually i've found that you could manually refresh it. now THIS tops it ofc :) their's one issue tough. as you are using a macro to refresh. you can't undo any wrongfully done action. which means you'll have to be quite awake to know which action you want to undo if any and what the previous data was

[@elfridhasman4181]

Thank you soo muchh😍🙏

[@ExcelCampus]

You're welcome! 😀

[@FEACInstitute]

Great video, but I have a question here in 2022. Using MAC Excel I am not allowed to change (General) to Worksheet. I get the message: Variable uses an Automation type not supported in Visual Basic. Any help? Thanks again.

[@kthlnmbr]

hi, thanks for the tutorial, one thing Ive notice in code “Dim p As Pivotcache……” Control Z/Undo was disabled,any work around on this please. Really bad your help. Thanks

[@rajeshn8751]

Hey Jon, runs perfectly ! But one important question, this code seem to refresh all the time and when you have large amounts of data, this can kill the processor, dont you think so. So my question is, just like we can set the refresh time for Power Query, how do we set a timer to refresh after 'x' time. Cheers-GK

[@Matheus654321ful]

Does It work for all pivot tables in the file, even those that are not related to the main data source? I have multiple date sources in my workbook. Would I have to repeat the process for each one?

[@bassamthebian9461]

Thank you Jon, that was really useful. However what did you mean we cant undo!! Do you mean when pivot table is refreshed it wont be changed again even once we re-change the data?? Thank you again

[@goonershezz9714]

Question: Jon or Anyone in the chatbox My data source is formulated (indirect lookups in multiple sheet) wide dataset which I turned into tabular data using power query and then pivot / charts to build a dashboard. Issue that I am having is that the macro is not recognising a change as the formula is still the same in the source data even though the numbers are changing so would I need to change the code or maybe a setting in the workbook? Thanks guys

[@derekblain]

Thanks for this Jon - great video. One follow up...my pivot table has a filtered selection (which remains constant) of row data and it seems each time I update the source file I need to click through to my filter selection w/in the table and select 'ok' before I'm able to refresh and see the data in a pivot format. Is there a workaround for that? I have several tabs of pivot tables and each time I need to go through that process. Thanks in advance.

[@leticiaramirez7668]

Loved, great teacher, thank you so mucho

[@ExcelCampus]

Thank you Leticia!

[@RArcher18]

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.

[@akashdeepsingghh]

Thanks very much, actually I have a table in GBP and linked that with the pivot and then I have linked the table with the Buttons so if I click button of USD it changes the data in table from GBP to USD. Then I have also put this VBA but it seems it just goes into loop and don't have any other option than to end task excel. Any way out?

[@gunderaodeshmukh9979]

Gud 1 Bro 🤝

[@dhankad10]

Hey, great solution. Only thing is that dropdown before you start writing code...(general) -> worksheet. I dont see this worksheet option. Anything I'm doing wrong or need to enable something

[@alanmitford]

Hi Jon, brilliant videos. The 3 part intro to pivot tables has been really helpful to me, thank you. I tried this video approach for adding new data into the set but it wasn't working for me and the new data wasn't appearing in the pivot tables... Am I right in thinking it's working in your video because you have manually set the pivot table data range to include a large number of empty cells (rather than the Ctrl+A approach) so that new added data falls within the defined pivot table range? Thanks again!!!

Thank you for the video. Is there a way i can set up to automatically refresh chart that connect to a pivot table?

[@alexlev8815]

I'm usually refreshing my pivot tables by adding macro to those sheets where pivot tables located when activating the sheet. It allows to me to keep undo history to source sheet while working on it. I use macro recorder to find out the name of pivot table I want to be refreshed as well as command to be used.

[@ExcelCampus]

Great tip! Thanks Alex. I'll do a follow-up on the Worksheet_Activate for the pivot table. We can also use the Worksheet_Deactivate event on the source data sheet to achieve something similar.

[@DiscoverTalent143]

In this example suppose if I have data in new sheet and that data auto paste one by one in data tab is it possible through macro? Please help

[@poojac25]

this is great tip and i have been looking for a solution for quiet some time. for a project i am working on , i need to create 3 pivot table with huge data. everytime i try to run this, my excel hangs. is there an alternative way?

[@mulavangue]

THANK YOU VERY MUCH

[@ExcelCampus]

You're welcome! 😀

[@roronoazoro-518]

What if the source data and Pivot table+Charts are in diff workbook. How can we create a macro for that?

[@jakubbiskupek4116]

Hello, how to do that if I created a data source based on cell values and the entire data in PQ are marked as connection only?

[@tristanquintus3200]

Useful code - thanks Jon, for the lesson.

[@ExcelCampus]

Thanks Tristan! :-)

[@thierryosborne1607]

Hello, my source data contains some blank cells. Obviously, I do not want to show them in the pivot table, so I filtered them out. The problem with this is since I do not have the (select all) option checked in my filtres, any new line of data is automatically filtered out as well, even if I refresh all data. Any idea on how to update and add new data to the table while hidding the blank cells ?

[@ricabude]

Congrats for the videos Jon. Happy sub here. I had a little problem when trying to select "worksheet" on the top left of visual basic. I get an error message - Variable uses an automation type not supported in visual basic. Any ideas to solve this? Thanks

[@janadidway5610]

Is there any way to have the filter update as well? I have a filter set to select all except blanks but anytime something is entered/changed on the table it is not picking up that change. I still have to go back into my pivot filter and select all but blanks.

[@gabriellamartinez9551]

This was the most understandable video I have come across in my journey to learn macros thus far! I do have a question specific to my work needs. I create pivot tables a lot based on a data sheet that is updated on a daily basis which originates from a webpage. I’d like to know if instead of adding the information to the end of the worksheet as you did in the video, is it recommended to just remove the old data and copy and paste the new data onto the macro enabled worksheet everyday? Or is there an easier way to go about this process?

[@TiMalice2009]

What about any pivot charts? Do they update too?

[@ExcelCampus]

Great question! Yes, the pivot charts are connected to the pivot tables. So they will update when the pivot table is updated.

[@Nadjuh]

Hell yes! I need this for work! :)

[@ExcelCampus]

Awesome! Thanks Nadja! :-)

[@vipyoo6511]

Thank you for the video. This is so helpful! After adding that macro in my excel, a MS sign-in page keeps popping up for me to sign in to a SharePoint site. How do I get rid of that pop-up? Thank you!

[@saviodcunha8741]

Thanks !!!

[@KarthiKeyan-ky9ts]

Hi John, I am in need of how to learn macro/VBA for beginners. Please share a video/ link to learn easier.

[@ExcelCampus]

Hi Karthi, We have a free 3-part video series on getting started with macros & VBA. Here is a link to first video. www.excelcampus.com/vba-training-my-first-macro/ I hope that helps. Thanks again and have a nice day! :-)

[@KarthiKeyan-ky9ts]

@@ExcelCampus Thanks for sharing the link -John. For my working process my supervisor need to automate the reports. May your feedback or response will be useful for me.

[@NigelMayhew1]

Hi Jon, your articles are great help to me. Thank you! I've a question, please. How can I use your code for refreshing the source data (PQ through SQL) first before refreshing the Pivot Tables (coming from Datamodel, and also stand alone), when I have the refresh data when file is opened activated?: It works very well when I manually click on the refresh all button. But when I close and reopen the file, in order for it to refresh the source data automatically, nothing happens. Can you please help?

[@rschultz7141]

Thanks. For some reason I get a runtime error. I might its due to my Dashboard which has several pivot tables which interact with data in not the normal manner. However a keyboard macro 'Refresh All' works fine. I tried the VBA you have on a simpler workbook and it worked fine. Any clues?

[@brightytclashing3898]

Mr Jon,, I opened a new excel sheet and I have been working on it for the last 8 hours but now it is saying not responding and the data is unsaved. It has been on the same for 30 minutes. How can I fix it instead of closing it to avoid redoing the work?? Kindly assist

[@randolina4449]

Love this - very helpful!

[@ExcelCampus]

Thanks so much R! :-)

[@KingGurrenAlex7]

i have an excel with 50-100K+ entries would this still function with that much information?

[@machoman7571]

Csn u tell me where is option data exvel pivot table video is

[@lindonhenry9628]

This will definitely make my life easier. Will the setting be saved in the worksheet and still work after closing and opening the document?

[@johnsonsathya]

Its very useful Thank you ....

[@orelshema]

ThankYouSoMuch!

[@javiff1]

Hi Jon, thanks very much for your videos. Nevertheless I got a problem: when I run this macro, I loose the select visible cell (the green rectangle) in all the workbooks I have currently open. I have to close all excel spreadsheets to have again the selected cell green rectangle visible... do you know how to fix this? Thanks in advance

[@rosiered3403]

Ahhhhhhhhhhh! I can't figure out how to create the VBA project editor or whatever it's called. Help!

[@MayLee-ow6pw]

What if my data source is in a different file? How do I make sure the pivot tables are updated when there is a change to the source data located in a different file?

[@aleronnunes4279]

Where do you add the macro for refreshing Pivot tables when you have data connections only.

[@aleronnunes4279]

Since it is Data Connection, there is no Sheet for Source Data

[@MrW0rDs]

I've copied the exact same code, and the Refreshall seems to be running in circle. Can't find a way to stop it, I have to close excel in the task manager in order to regain control... Any idea what's the matter ?

[@Ifritlordofire]

Did you manage to solve your problem?

[@MrW0rDs]

@@Ifritlordofire Sorry I can't recall. I'm a dev now and I want to avoid VBA at all cost it's just a real pain to use

[@Ifritlordofire]

@@MrW0rDs Ahah you're not the first dev who told me that! It's alright, thank you anyway!

[@alexdagreatpeter1579]

Hello Sir! I need your help. How to contact you? Thank you.

[@th3scholar100]

Hi Jon, The VBA is not picking up updates in a column that contains a Boolean T/F that references a driver cell that contains geographies. I manually changed one of the cells with new data and did see the VBA pick up that update. Can you help with a solution?

[@axion8788]

Super helpful but.... How can "Refresh Pivot Table when source data changes" not be a simple, user-selectable option for each pivot table? This is dangerous since the moment you change a value in the source data all dependent pivot tables show incorrect data. In fact, it the refresh should be automatic with an option to turn it off. End of rant.

[@abuena73]

Hello - what could be the macro script if the source data is coming from another file saved as xlsx, and you have separate file saved as xlsm to contain the pivot with charts? So basically, if i add new entry from my xlsx data source, then my xlsm with pivot and chart will automatically get refreshed; is that possible?

[@k59ngothithuynhi7]

how to do if I just want to refresh 1 table in 1 sheet? I want t create a button then if I click, that workbook will refresh

[@FuserLimon]

This was very useful. Thank you so much.

[@isaidstream4547]

Hello I have a program that is constantly saving data to my CSV file, and works perfect, but excel dont't let to refresh every 1 second automatically, I have to do it manual, help me pls. You are the excel god

[@saudasad5600]

How can i refresh data in pivot from different sheets directly without updating it in the data sheet of pivot???

[@HoffeeCakes]

Hi Jon, quick question. I've been watching videos all day and I know it has to be easier than this... I have a data sheet with data entered monthly and a dashboard with a slicer and pivot table connected to that data sheet. I would like my slicer to change months as data is entered for that month on the data sheet. So, when I enter data for May, the slicer button changes to May and the data with it. When June comes, it changes to June and so on. The code I have is close, but it either stops at Jan or plows all the way to Dec - which of course has no data yet. Thank you!

[@susanknoll5135]

Thank you very big help

[@eduardoantoniogarcia2689]

What happen if your data source is a Access database, how Refresh the dinamic table?

[@allabout1135]

I hawe source and pivot on the same sheet. And this method do not work for me. I got an error. Any suggestion?

[@ExcelCampus]

Hi Aleksandrs, Sorry I forgot to mention that potential issue. The event gets stuck in a recursive loop when the data is on the same sheet as a pivot table. So, we just need to disable events before the RefreshAll method, then re-enable them. Here is the code. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ThisWorkbook.RefreshAll Application.EnableEvents = True End Sub I hope that helps. Thanks! :-)

[@aviroy7085]

Hi Jon, Thank you for the video :) , I was adding some data in the source tab but can not see any changes in the pivot table. Would you please help on that

[@Koos971]

Is your source data set as a table in excel? that is a common issue with pivots not updating. In case you haven't done so already: click anywhere within the source data, under "Insert" on the ribbon, click "table", check "My table has headers" and hit "OK". Click anywhere within your source data, then insert pivot table...that pivot table should update as you add information, rows & columns to your source data...you will have to refresh the pivot table to see changes

[@aviroy7085]

@@Koos971 Thank you 😃👍

[@CleverLittleVixen]

I manually have to sort my row labels in my pivot table which is fine, however after I get them where I want them, then later connect a slicer to the pivot table, depending on the values each row comes up with when sliced it will rearrange my row labels back to the default (alphabetical position) which I do not want to happen. SO FRUSTRATING! How do I prevent my manually sorted row labels from rearranging back to the default after those values have been filtered through a connected slicer?? thank you!

[@torukmakto0]

Thanks a lot

[@joesmith5801]

So I have an issue that I was hoping this would solve, and it doesn't. Please help! I have a single cell table, that is the source for a power query. the power query is altered in such a manner that there is no headers and the result set is only the value in the single cell table. thsi power query is used to filter 2 other power queries that have SQL statement sources. I have a button that triggers a macro, and I want the first power query to refresh, then the subsequent 2 power queries, then 3 pivot tables that draw from the previous 2 PQ's. I need these things to happen in a sequence, not in a race. When I use refresh all it triggers everything at the same time, the PT's finish first and end up with inaccurate data, as the underlying PQ's took longer to refresh. Furthermore, when I refresh my PQ's they want to come to the foreground and be seen. The sheet they are on becomes activated. If the sheet is hidden the active sheet becomes inactive, and excel seems to freeze. Thats not the big issue, the big issue si the refreshing. But if you could solve that mystery too, that would be great. Any and all help is greatly appreciated!

[@puneetvij8433]

Sir John, Important one, thanks ! ; )

[@ExcelCampus]

Thanks Puneet! :-)

[@swheeler6848]

thank you!

[@drajune]

Thanks

[@adrianwara3796]

What if my source data originates from multiple sheets? Example: Cell A1 is: =SUM(Januar:Desember!K6) When i make a direct change to my source data my pivottable will update. When i make a change in any of the sheets from january to Decmber, it wont update untill i manually press refresh. I have already typed your code. Thanks!

[@AnyehongSeo]

Is it possible to use a command button on a locked sheet?..and how?

[@MG-by9zw]

Do you know how I can link a Macro with the pivot table filters? I.e. if we filter any value the VBa runs the code.

[@MemphianSounds]

What if your data source is an external CSV file?

[@envise]

Hi, thanks for sharing this powerful tip! In my situation, the data sheet is updated by formula based on parameters from another sheet. So, macro is not getting executed. If I manually edit cells in the data then it works as expected. Do you have any tips for detecting data refresh that happens indirectly through formula? Thanks!

[@張育銘-p8u]

It's a great and incredibly easy macro! Omg

[@ExcelCampus]

Thanks! :-)

[@jonelles7223]

Hello Jon, I just find out about your wonderfull video's. I'm from Belgium and I'm creating a dashboard for a friend of mine. I implemented the macro and it's working fine on all my pivot tables except when I'm adding records to my data worksheet. Are you also familiar with this problem? Greetings, Jo