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! :)
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.
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.
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
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?
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!
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! 🙂
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
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!!!
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.
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.
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
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
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.
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
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.
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.
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?
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
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
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
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?
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!
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
4 роки тому
Thank you for the video. Is there a way i can set up to automatically refresh chart that connect to a pivot table?
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?
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.
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?
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.
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?
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 ?
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! :-)
@@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.
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
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
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?
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
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
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?
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!
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!
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?
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!
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!
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!
If you only knew how long I've been looking for someone to explain this correctly lol. Literally took like 1 minute. Thank you!
This Saved my probably 2 hours of work - Very simple trick
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.
The best video and explanation on youtube.. it worked great and right away.. Thankss
Great teaching voice. You're right to the point and easy to follow along! Cheers, Scotty
Thank you kindly! 😀
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! :)
I love this vba macro. the most basic and simplest way for my need now. Thank You
This is a well produced and helpful video. Thank you, Jon!
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.
You're the guy Jon when it comes to VBA tips & tricks; hands down. Always right to the point - THANK YOU.
I appreciate that, Qomri! 👍
Thank you so much! So simple and yet and I spent so much time looking for this solution, you've brightened my day :)
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.
Thank you sir for making our life easier
Thank you Jon, you explain things so well. I had been searching for an answer on auto refreshing queries.
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
Thanks Jon. That was very Helpful since I do not know any VBA. So this is very helpful for my users. :)
Awesome! Happy to hear it helps you and your users. :-)
Well explained and useful too. Solves the purpose. Thanks.
Thanks brother love from India
Thanks so much!!!! Game changer ;) Regards from Poland
Much appreciated! Fit for a total beginner in Macros
Hi Jon.. nice tip on refresh.. will come in handy for certain applications. Thanks again and Thumbs up!
Thank you Wayne! :-)
thank you! liked and subscribed.
Excellent lesson easier excel life..
hey Jon, thanks for putting this video, its really very helpful..
thanks Man
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?
refreshing pivot tables is still a problem, and your video shows how easy it can be dealt with;)
Thank you Adam! :-)
this works on excel online? great video!
Very good thanks
Saved my life
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!
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! 🙂
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
Gud 1 Bro 🤝
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!!!
This will definitely make my life easier. Will the setting be saved in the worksheet and still work after closing and opening the document?
Thank you soo muchh😍🙏
You're welcome! 😀
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.
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.
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
Works great. Thanks.
You're welcome! 😀
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
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.
Thanks !!!
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
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.
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.
Loved, great teacher, thank you so mucho
Thank you Leticia!
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?
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
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
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
ThankYouSoMuch!
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?
THANK YOU VERY MUCH
You're welcome! 😀
Thanks
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!
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
Thank you for the video. Is there a way i can set up to automatically refresh chart that connect to a pivot table?
Thanks a lot
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?
thank you!
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.
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?
Its very useful Thank you ....
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.
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
Useful code - thanks Jon, for the lesson.
Thanks Tristan! :-)
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?
Hell yes! I need this for work! :)
Awesome! Thanks Nadja! :-)
thanks...
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 ?
Hi John,
I am in need of how to learn macro/VBA for beginners.
Please share a video/ link to learn easier.
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! :-)
@@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.
What if the source data and Pivot table+Charts are in diff workbook. How can we create a macro for that?
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?
Thank you very big help
Love this - very helpful!
Thanks so much R! :-)
It's a great and incredibly easy macro! Omg
Thanks! :-)
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
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
i have an excel with 50-100K+ entries would this still function with that much information?
This was very useful. Thank you so much.
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?
What about any pivot charts? Do they update too?
Great question! Yes, the pivot charts are connected to the pivot tables. So they will update when the pivot table is updated.
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
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
@@Koos971 Thank you 😃👍
VBA is a powerful tool. But I cant understand why microsoft dont make this auto update option as a basic feature in excel, its so much needed nowadays
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?
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!
Csn u tell me where is option data exvel pivot table video is
Sir John,
Important one, thanks ! ; )
Thanks Puneet! :-)
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!
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?
Thank you
love this , thanks
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!
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!
Ahhhhhhhhhhh! I can't figure out how to create the VBA project editor or whatever it's called. Help!
Amazing. 🌟 🌟 🌟 🌟 🌟. Thanks
K D က်မက ေအးေဆးကိုယ့္အခန္း
ကို႔အိမ္မွာေနတယ္
ျပသာနာ႐ွၿပီး က်မဆီေရာက္လာပိုက္လာေတာင္းသူေတြက လူလိမ္ေတြ မေကာင္းတဲ့မိသားစုအမ်ိဳးေတြ
ထိုင္းမွာအခုခိုေအာင္းေနတယ္
လက္ခံတဲ့သေဌးေတြ သူိခုး
လူသတ္သမား
ေအာက္ေကာ့ရင္းေစာေစးတို႔အမ်ိဳးအဖြဲ႔အစည္းက ျမန္မာႏိုင္ငံမွာ ပတ္စ္ပိုျဖန္ခ်ီေနတာမသိဘူးလား
ဖားမ icjၾကားျဖတ္လိုက္လြင့္ေနတာ
See more
ဖုန္းတုင္းကို ဆက္ခ်ိတ္ေနတာ
Where do you add the macro for refreshing Pivot tables when you have data connections only.
Since it is Data Connection, there is no Sheet for Source Data
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
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!
What happen if your data source is a Access database, how Refresh the dinamic table?
How can i refresh data in pivot from different sheets directly without updating it in the data sheet of pivot???