Locked lesson.
About this lesson
Automating PivotTable refresh operations.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Automating Refresh.xlsx265 KB Automating Refresh - Completed.xlsm
271.1 KB
Quick reference
Automating Refresh
Topic
Automating Pivot Table refresh operations.
Where/when to use the technique
You want to automatically refresh your Pivot Table data to make sure it’s always current.
Instructions
Refresh at file open
- Right click the PivotTable > PivotTable Options > Data > Refresh data when opening the file
- Note that this must be done for each data source
- This works well for PivotTables connected to databases
Method 1: Refresh each time the PivotTable’s worksheet is selected
- Go to the Developer tab > Visual Basic > Expand the VBAProject > Microsoft Excel Objects
- Double click the worksheet that holds your pivot and paste in the following code:
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("pvtSales").PivotCache.Refresh
End Sub
- Update pvtSales to the name of your PivotTable
- Close the editor
- This can be done for each sheet you’d like to refresh automatically
Method 2: Refresh every pivot when any sheet is selected
- Go to the Developer tab > Visual Basic > Expand the VBAProject > Microsoft Excel Objects
- Double click the ThisWorkbook object paste in the following code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWorkbook.RefreshAll
End Sub
- Close the editor
Key points to remember
- You will get macro prompts on your file from this point forward
- Clicking your pivot table will start a refresh (can be an issue if data sets are huge)
- Use either Method 1 or Method 2, NEVER both
- There may only be one Worksheet_Activate event in any module. (If you already have one, you may need help adding this code. Find a forum and ask!)
- There may only be one Workbook_SheetActivate event in the ThisWorkbook module. (If you already have one, you may need help adding this code. Find a forum and ask!)
- 00:00 In this video, we're gonna look at various ways to automate
- 00:05 the refresh of pivot tables so they always stay current.
- 00:09 Just by way of understanding what we have here, I have a report worksheet,
- 00:13 which has two pivot tables on it.
- 00:15 The one on the left is linked to the data on the ChitDetails page.
- 00:18 The one on the right is linked to the data on the Covers page.
- 00:22 Two different datasets.
- 00:24 The first thing that we can do is if we were connected to a database,
- 00:28 we could right-click on our pivot table say, PivotTable Options.
- 00:31 Go to Data and we could choose to refresh the data when opening the file.
- 00:37 And that way, every time the file is opened it would automatically communicate
- 00:40 with database and stream all the records back in and we'd be good to go.
- 00:43 Unfortunately, when we are working with tables that are housed inside
- 00:47 the workbook, that won't work.
- 00:48 There's no automatic refresh capability built-in to a pivot table.
- 00:52 So in order to do that, we actually need to record a macro.
- 00:56 Now if you don't have the developer tag showing at the top here,
- 01:00 you need to go and right-click on one of the tabs at the top,
- 01:04 go to customize ribbon and show the developer tab and say okay.
- 01:08 And that will give you this new and
- 01:10 ultra powerful tab in here that allows you to do things like record macros.
- 01:13 So what we're going to do is go to Record Macro and
- 01:17 we can leave it with Macro1 as a name, that's fine, say OK.
- 01:21 We'll right-click on our pivot table and we'll choose Refresh and
- 01:25 then we can go to Stop Recording and we'll click on the button that says,
- 01:30 Visual Basic and that will launch us into the Visual Basic editor.
- 01:35 If you don't have this window on the left-hand side, you can get it by saying,
- 01:39 View and Project Explorer and that will bring up this window.
- 01:43 It doesn't have to look exactly the way that I've got mind set,
- 01:47 I do a lot of work in VBA.
- 01:48 But in this particular case, what we're most interested in is the fact that we've
- 01:52 got a VBA project for the workbook that we're working with and
- 01:55 we can expand the tree to show all the different components of it.
- 01:58 I liked to drill into module one, because that's where my recorded code will go.
- 02:02 The code that you record may look like this or it may have some extra lines to
- 02:06 it, but the key component that we want is this one.
- 02:09 ActiveSsheet.PivotTables, the name of your pivot table, .PivotCache.Refresh.
- 02:14 I'm going to copy this code and then I'm gonna go to the worksheet
- 02:18 that I want to have refresh automatically, which is Report.
- 02:22 Double-click on that and you'll notice there's nothing inside here.
- 02:26 From the General drop-down, I'm gonna pick up Worksheet and
- 02:30 instead of SelectionChange, I would like to go with worksheet Activate.
- 02:35 So every time the worksheet is activated,
- 02:37 I would like to run this particular piece of code.
- 02:41 ActiveSheet.PivotTables.PivotCache.Refr- esh.
- 02:44 And because I've got a Private Sub with nothing in between that and
- 02:48 the End Sub line, I'm just gonna clean this up and remove it.
- 02:51 I didn't need it.
- 02:53 Now, I can minimize the Visual Basic editor and
- 02:56 I'm gonna prove that this will actually work.
- 02:59 We'll go to take a quick look at our sandwiches line down here.
- 03:03 So we've got 400, 900, $1,100.
- 03:07 I'm gonna put a ridiculous amount in here.
- 03:11 So we'll go to ChitDetails and we'll change the sandwich to $20,000.
- 03:14 Now when I go back to Report, you'll notice that it's automatically updated.
- 03:18 And if I come back and put in $4.50, when I go and
- 03:21 activate this worksheet, it will update the pivot table and that's cool.
- 03:26 But what about Covers?
- 03:28 Let's go do the same thing.
- 03:29 We'll put in a ridiculous amount of covers on week 1, 20,000 covers.
- 03:33 No refresh.
- 03:36 So how can we deal with that?
- 03:39 Well, we could record a macro and do the same thing or
- 03:41 we could decide you know what?
- 03:42 Maybe I'd like to refresh all the macros in my work or all the pivot tables in my
- 03:46 workbook at once, so I'm gonna go that route right now.
- 03:49 Every time I select any worksheet, I'm gonna update the pivot tables.
- 03:53 This is something I definitely do not want to do if I'm using massive sets of data
- 03:56 from a database, because that really slows things down.
- 03:58 But in the case of a small set of data in the tables, this should be okay.
- 04:02 So what we'll do is go to Record Macro, Macro2, that's fine.
- 04:08 We'll go to Data, Refresh All.
- 04:10 That will refresh all the pivot sources.
- 04:12 Back to the Developer tab, Stop Recording.
- 04:17 Back into the Visual Basic editor.
- 04:18 Back into Module 1, because this is where our code gets recorded.
- 04:22 And you can see we've got an ActiveWorkbook.RefreshAll, so
- 04:25 I'm gonna copy this.
- 04:26 Now because I've already put one in for this report, I'm gonna go and delete this.
- 04:31 I don't want to have this automatically running both pieces every time or
- 04:34 I get duplication.
- 04:35 I'm going to do something special with this.
- 04:37 The all stuff goes in this workbook.
- 04:39 We're gonna click on General.
- 04:43 We're gonna say, Workbook and instead of Workbook open,
- 04:45 although this would work for when we open the Workbook.
- 04:48 We're going to choose workbook SheetActivate and
- 04:51 then we're gonna paste the code in here and I'll get rid of the workbook open.
- 04:56 So every time a worksheet is activated, I would like to refresh all the data and
- 05:01 now you'll see, oh, look, I've got 20,000 covers.
- 05:04 If I go back and set this down to two covers, Enter.
- 05:10 It's updated the pivot table.
- 05:11 Notice that I don't have a bunch of sandwiches any more over here.
- 05:14 Let's go and throw $20,000 in the sandwiches line and Report and
- 05:19 it's updated as well.
- 05:21 So, every worksheet updates.
- 05:22 The big key that I want you to be aware of is use one of these or the other.
- 05:26 You can either setup a Worksheet activate for each report that you want or
- 05:30 rather one worksheet activate that always calls each individual line of code that
- 05:35 looks like this.
- 05:36 So you could have multiple pivot tables that you were actually using,
- 05:39 if you wanted to do it this way here and pivot number two or whatever it's called.
- 05:43 You could run that or you do the ActiveWorkbook.RefreshAll.
- 05:47 Don't do both or you'll end up with a big set of duplication.
- 05:50 And if you have either of these things in your workbooks,
- 05:53 like a workbook SheetActivate, go to a forum and get some help implementing it.
- 05:58 VBA is a difficult technology.
- 05:59 Using these techniques as bite-sized pieces is really useful.
- 06:02 But when you start getting into further customization, it can get quite difficult.
Lesson notes are only available for subscribers.