Refresh Pivot Table Automatically when Source Data Changes

The Opinion Lighthouse
32 followers
91 Video Views·Nov 8, 2022
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 the 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 .xlsm extension.
If you're not familiar with that I have a whole other 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 VB editor, developer tab, visual basic
button, keyboard shortcut is alt f11 and within the VB editor here on the left side in 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
anytime we edit a cell or multiple cells or make changes to 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
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 to
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 VB 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 break
point 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. Now we'll add this break point or this stop point
right here to the top and now when we go back over to the worksheet, the data sheet 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
we'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 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 okay 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 okay on that. It just lets you know that any time 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 the 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 break
point 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 tables. We're actually refreshing the pivot cache that's connected
to the pivot tables. 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.
And 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 and you have that set up, 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 below. If you enjoyed that video, there are a few simple things you can do
to help me out. If you're 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.
