Did you like how we did? Rate your experience!

Rated 4.5 out of 5 stars by our customers 561

Video instructions and help with filling out and completing Fill Form 8815 Reload

Instructions and Help about Fill Form 8815 Reload

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.

If you believe that this page should be taken down, please follow our DMCA take down process here.