Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

About this lesson

Automating Pivot Table refresh operations.

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!)
  • 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.

Gift this course