Locked lesson.

Upgrade

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.xlsx
265 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!)
Login to download
  • 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.