You'd have to pick a different event for the macro to run against. I chose the worksheet_deactivate event but there are others however I'm not sure if any of the other events would work from a logic or performance point of view for example the SelectionChange event runs when the cursor moves from one cell to another nut do you really want the refresh happening every time a different cell is selected?
@catalinalemus6214 If the Pivot Table was on the same sheet you would need to find an appropriate "event" for the VBA code to hook into (in my example it was switching from one sheet to another) so I think it's easier if the Pivot Table is on a separate sheet but in theory it should work on the same sheet
Good Day, after saving my excel workbook and exiting, upon reopening the document the macro is gone. is there a permanent solution to keep it there always
@stefankleinhans4895 If the file was an XLSX file, once you have added a macro to it you must "Save As" and choose XLSM (Macro Enabled Workbook) as the file type. If you re-save as an XLSX file the macro is lost because Excel does not let you save macros in XLSX files.
@DJKnightrin Yes it does - On Windows! The VBA code refreshes all pivot tables in the file not matter whether they are "classic" ones or power pivot ones. On a Mac, if you manually do Data > Refresh All you get an error message because Excel for Mac doesn't support the Data Model/Power Pivot. However if I create a macro that automates RefreshAll and run the macro I don't get an error - but whether the Pivot Table has refreshed I don't know
This was the perfect refresher course! Thank you so much!
Bro, excellent job. Went through several other vids that didn't do as quick or thorough of a job. Thank you!
Thank Mike, excellent and very helpful video.
Very succint, interesting, and most importantly, useful. Thanks Mike!
Very welcome!
Thanks Mike for this video. Well done.
Very welcome
Great video. Thanks Mike!!
Hello, does this refresh in the background without having to open the file?
@Ohhkaleyeahh Hi. No the file has to be open to do the refresh but you could tick a box so that it refreshes automatically when the file is opened
Thanks Mike - how do you make the auto refresh occur without needing to switch worksheets?
You'd have to pick a different event for the macro to run against. I chose the worksheet_deactivate event but there are others however I'm not sure if any of the other events would work from a logic or performance point of view for example the SelectionChange event runs when the cursor moves from one cell to another nut do you really want the refresh happening every time a different cell is selected?
thanks, very helpfull
You're welcome!
Hi Mike. One question, does the pivot table have to be on another sheet?
@catalinalemus6214 If the Pivot Table was on the same sheet you would need to find an appropriate "event" for the VBA code to hook into (in my example it was switching from one sheet to another) so I think it's easier if the Pivot Table is on a separate sheet but in theory it should work on the same sheet
@@MikeThomas67 It worked. You just have to switch sheets for a moment in order for it to work and refresh the pivot table.
@catalinalemus6214 That's called logical thinking - well done!
Good Day, after saving my excel workbook and exiting, upon reopening the document the macro is gone. is there a permanent solution to keep it there always
@stefankleinhans4895 If the file was an XLSX file, once you have added a macro to it you must "Save As" and choose XLSM (Macro Enabled Workbook) as the file type. If you re-save as an XLSX file the macro is lost because Excel does not let you save macros in XLSX files.
Can this work with power pivot table?
@DJKnightrin Yes it does - On Windows! The VBA code refreshes all pivot tables in the file not matter whether they are "classic" ones or power pivot ones. On a Mac, if you manually do Data > Refresh All you get an error message because Excel for Mac doesn't support the Data Model/Power Pivot. However if I create a macro that automates RefreshAll and run the macro I don't get an error - but whether the Pivot Table has refreshed I don't know