Refresh Pivot Table Automatically when Source Data Changes

welcome to Excel campus my name is John and in this video I'm going to explain how to automatically update your pivot tables when the source data changes so here we have a simple pivot table and on this data sheet over here we have the source data for the pivot table and anytime we make a change to this source data range whether we edit a cell or copy and paste new data below the table we want our pivot table to automatically update so in order to do this we're going to use a simple macro and I'll explain how that works the first thing you'll want to do is save your workbook as a macro enabled workbook you can see I have this workbook here as a dot s xlsm extension if you're not familiar with that I have a whole nother video series that explains how to get started with macros in VBA and create macro enabled workbooks and I'll put a link to that in the description below this video so we're going to open the VBA editor Developer tab visual basic button keyboard shortcut is alt f11 and within the VBA editor here on the left side and the project Explorer window you want to find the workbook that you're working in that contains the pivot tables and then within that we want to find the sheet object that contains the source data so here within the Microsoft Excel objects folder this might be collapsed you can go ahead and expand it and then here is our sheet the data sheet contains that source data so we're going to double click double left-click on that and that's going to open the code module for that sheet object and then here within the code modules where we're going to add our macro now we're going to add an event and to do that we'll first click this drop down here and we're going to choose worksheet that'll add the worksheet selection change event here or this macro we don't actually want that one so from within this drop down over here the procedure drop down we're going to select change select the change event right there that will add the worksheet change event right here and this is the one we want we'll go ahead and delete the selection change event because we don't need that we don't want this to get confusing so the worksheet change event this macro will run anytime we make a change to this worksheet and specifically any time we edit a cell or multiple cells or make changes two cells within that worksheet this macro will run so this is where we want to put the code to refresh our pivot tables and that can be done with one very simple line of code which is this workbook or just reference the workbook this workbook dot refresh all the refresh all method is going to refresh all the pivot tables any queries and connections data connections that you have in the workbook it will refresh all of those when we run this line of code it's the same as if you're in Excel and you hit the Refresh all button on the data tab of the ribbon so that's all we really need there let's go ahead and test it out so we'll jump back over to Excel again we're on our data sheet here and if we just make a change to any one of these cells here let's just make a change of this cell and we'll make it a really big number so we can definitely see a change in our pivot table and I'll hit enter and once I hit enter there that code runs to refresh all the pivot tables in the workbook if we go over here to our pivot table we can now see that it's automatically been updated right here here's that new value of two million that we just added and I want to share one little trick with these events so we'll jump back to the VBA editor here if you want to make sure the event is firing when we make a change and this macro is running you can add a breakpoint right here to the beginning or the top line of the macro just left click right here in the column over here to the left of the code module and now we'll add this breakpoint or the stop point right here to the top and now when we go back over to the worksheet the datasheet and make a change actually let's go over to this new data sheet here we'll copy and paste some data below in that source data range so we'll go over to our data sheet here we'll go down to the very bottom and it'll just paste some new data right here I'm going to hit control V to paste and you can see when I did that I just hit control V we get this stop or this break right here in our code we can see this yellow line is highlighted this means this code has started running and now we can either hit f8 on the keyboard to step through each line of code now we only have one line of code here so I'm going to hit f8 that'll highlight this line of code hit f8 again that will run that line of code refresh all of the pivot tables in the workbook and then I'll hit f8 again to stop running that so that's one way to just stop this and debug this and also make sure that your worksheet change event is firing or it's running when you make a change to the worksheet if it's not you might not have the macros enabled in the workbook so you might need to close the workbook reopen it and make sure you enable macros and then your worksheet change event should start working when you make any changes to the source data sheet here in the workbook and then another important thing to note here is since we are running a macro to refresh all of the pivot tables we do lose the undo history when we make this change and the macro runs that's just a side effect something that's built into VBA that we don't really have much control over in this case so it's just good to know that that we don't have the ability to undo those changes we make in this specific sheet here if you wanted to have another kind of workaround to that one thing you can do is if you go over to the pivot table and just select any cell inside the pivot table go to the analyze tab here and then open the pivot table options there is another option here on the data tab to refresh the data when opening the file you can check this here and click OK and that will turn that option on to refresh the data when opening the file and there and you get this little warning message here and just hit OK on that it just lets you know that anytime the file is opened the pivot tables will automatically refresh now that does not mean that the data will refresh when we make changes to the source data range so again if you want to do that if you have users working in this and you want to just make sure that pivot tables are updated every time they make a change here then you will need to use that macro code this macro code here that we just saw will definitely need to use that but if you are just sending your files out and you want to make sure those pivot tables are updated you can use that pivot table option as another alternative so those are two different ways to go about it and then one last thing here is if you don't want this code to stop every time you run it you can just left-click this breakpoint again and that will clear that and now every time we make a change in our source data range if we go back let's just go back up to the top here we'll change this back to 150 hit enter that code will run the pivot tables will refresh and we can go back over to this tab here and see that our numbers have been restored to what they previously were and then finally I just wanted to share a few other ways to refresh pivot tables we just saw this line of code which will refresh all the pivot tables it will also refresh all the queries if you're using power query or refresh all those queries or any data connections as well if you don't want that you can just refresh the pivot tables only and I added some code down here I'll make this file available for download and put a link in the description below this video so here's a few different ways to just refresh the pivot table so we're actually refreshing the pivot cash that's connected to the pivot tables this this section of code here will loop through and refresh all the pivot caches in the workbook so you could put this code right up here in the worksheet change event instead you can put that code up there if you just want to refresh one single pivot table you can use something like this line of code here which references the pivot table on a specific sheet references it by name or you can reference it by index number and then refresh its pivot cache again you could add that line of code there to the worksheet change event this line of code right here you could just put that up here and you would not need the this workbook dot refresh all and then I also have another article that explains disabling the background refresh if you are using power query and that will allow your queries to refresh before your pivot tables so if you're interested in that you have that setup check out this article as well I got a link right here I'll put a link in the description below the video as well and that will help you get all of that set up so I hope that helps you get your pivot tables refreshed when any changes are made to your source data range of course if you have any questions please leave a comment if you enjoyed that video there are a few simple things you can do to help me out if you are watching this video on youtube click the like button below the video and leave a comment with any questions or feedback and please don't forget to subscribe to my free email newsletter to get more tips and tricks that will help you learn Excel thanks again for watching and I'll see you soon

Channel: Excel Campus - Jon

Views: 268,617

Rating: 4.9150672 out of 5

Keywords: excel, excel campus, pivot table, refresh pivot table automatically, excel pivot table, refresh pivot table automatically when data changes vba, refresh pivot table automatically when data changes, refresh pivot table automatically vba, automatically refresh pivot table when data in a sheet changes, excel vba programming, excel pivot table tricks, pivot table excel, excel campus macros, excel campus pivot tables, update pivot table automatically, worksheet change event vba

Id: NI1UQcYtl1A

Length: 9min 14sec (554 seconds)

Published: Mon Sep 10 2018