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.xlsx272.9 KB Automating Refresh - Completed.xlsm
279.6 KB
Quick reference
Automating Refresh
Automating PivotTable refresh operations.
Where/when to use the technique
You want to automatically refresh your PivotTable 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:04 One of the things that's kind of frustrating about a pivot table is that you have to
- 00:08 manually go and click the Refresh button in order to get them to update.
- 00:11 Now, if you have a pivot table that's bound against an external data source like
- 00:16 a database, you can actually go in right click and say PivotTable options.
- 00:21 Go to Data and choose to Refresh the data when opening the file.
- 00:25 That'll work great every time you open the workbook it'll drive the refresh and
- 00:28 everything is good providing the data is not changing on a regular basis.
- 00:31 The problem is, the data in this workbook where we have two separate pivot tables
- 00:36 here that are based off of separate data sources in the Excel workbook.
- 00:40 Refreshing it opens great but
- 00:42 the reality is people are going to change this as we go along.
- 00:45 And what we'd like is a way to automate it so
- 00:47 that it automatically refreshes the pivot table.
- 00:49 In order to do that, you need to record a macro.
- 00:51 And the first thing that we're going to need to make that happen is the Developer
- 00:55 tab, which I don't see on my ribbon.
- 00:57 So to get the Developer tab, we're going to right click on one of our tabs
- 01:01 and we're going to choose Customize the Ribbon.
- 01:03 This will take us into the ribbon dialogue here, and
- 01:07 we can check the box next to developer and say Ok.
- 01:10 You'll then have a new tab in your ribbon called Developer, and
- 01:14 this gives you access to the ability to record macros.
- 01:18 What I'm going to do is I'm going to record a macro right now, and
- 01:21 I'll call it Macro1 and store it in this workbook that's fine.
- 01:24 We'll say Ok, and what I'll do now is right click on my pivot table and
- 01:29 choose Refresh.
- 01:30 I am now going to choose, Stop Recording.
- 01:33 Now, all I have is driven a refresh for this pivot table.
- 01:36 We can't really see that anything has happened, but that's okay.
- 01:39 What we are going to do now is jump into the visual basic editor and
- 01:42 make sure that this code gets into the right spot.
- 01:45 If you've never been in the Visual Basic Editor before, you may not have this
- 01:48 project window on the side here and you do kind of need that.
- 01:51 So you're going to go to View and you can go to say,
- 01:54 Show me the Project Explorer or press Ctrl+R.
- 01:57 This gives you access to all of the different components that are inside
- 02:01 the actual workbook that we actually need to get to.
- 02:03 Where we're going?
- 02:05 Is it into the modules?
- 02:06 And we're going to double-click on Module1, and
- 02:09 this is the code that got recorded when we actually worked on these things.
- 02:13 ActiveSheet.PivotTables.pvtSales.PivotCac- he.Refresh.
- 02:18 What we're going to do is we're going to actually take this particular block of
- 02:22 code here.
- 02:22 We're going to say Copy.
- 02:24 And we're going to go over to the actual worksheet where we need to have this
- 02:28 code run.
- 02:28 So it's our report worksheet is where the code is actually going to run.
- 02:31 I'm going to double click on that.
- 02:32 And under declarations, I'm not going to see anything, but
- 02:37 under General, you'll see that we have the option to go to worksheet.
- 02:42 The worksheet automatically comes up with a worksheet selection change event.
- 02:46 I'm going to change this to say I'd like a worksheet, activate event.
- 02:51 And what's going to happen is every time I activate the specific worksheet,
- 02:56 we're going to run a particular piece of code.
- 02:59 That piece of code is going to be ActiveSheet.PivotTables(pvcSales).PivotCa-
- 03:05 che.Refresh.
- 03:06 So, this is what we're going to do to get the code into the right place.
- 03:09 Now, we can minimize this particular window.
- 03:12 How do we know if it worked?
- 03:14 This pivot table is built off of the ChitDetails here.
- 03:16 So what I'm going to do, I'm going to come over we have a breakfast item here, and
- 03:20 I'm going to put a ridiculous amount in here.
- 03:22 We're going to put $20,000 into our sides of toast for this particular day.
- 03:27 So if all goes well for week one, when we go back to our report,
- 03:30 we should see that we've got at least $20,000 worth of sales.
- 03:34 And if I go back to my Report page now, you'll notice that it immediately updated.
- 03:38 So that's pretty cool.
- 03:40 Let's go to the Covers page for a second though.
- 03:42 And let's go and say hey, you know what,
- 03:44 why don't we throw say $40,000 worth of beverage sales in here?
- 03:48 We'll go back to our report.
- 03:50 And it doesn't look like anything's changed here, so this guy is not working.
- 03:54 Now, I could obviously go and record a macro to do the refresh on these, but
- 03:58 what I'd really like to do at this point is actually record a different macro.
- 04:03 I'm going to go Record Macro again, this one will be Macro2.
- 04:07 And what I'm going to do now, is go Data, Refresh All.
- 04:11 Notice the pivot table did update.
- 04:13 So we're going to go back to the Developer tab and stop recording the macro.
- 04:19 Back to Visual Basic, and back to Module1 where our code got record.
- 04:23 Notice this block of code ActiveWorkbook.Refreshall.
- 04:27 This will refresh all pivot tables.
- 04:30 But I actually want to do this in a different place.
- 04:32 We could do it in the worksheet activate, that will absolutely work.
- 04:36 I'm actually going to go back right now, I'm just going to delete this code
- 04:39 because I'm going to show you somewhere else where you can make it work.
- 04:41 because this is any time any worksheet is changed,
- 04:44 would be under this workbook module.
- 04:47 We'll go into Workbook, which gives me Workbook_Open, but instead,
- 04:52 I'm going to say Workbook_SheetActivate.
- 04:55 So any time a worksheet is activated,
- 04:58 we're going to go in and do a full Refresh All.
- 05:02 And what we can see at this point is if I now go and take a look at this,
- 05:05 a RefreshAll might run.
- 05:06 If I go back to covers and say, hey, you know what, this was actually $234.
- 05:12 When I come over to my report now,
- 05:14 you'll notice that it's right back down to where it was.
- 05:17 And if I go to ChitDetails and set this back to a 1.5, for example,
- 05:21 When I come back to my report, you can see that it's updated.
- 05:26 Now, you'd never want to do this for a very, very large database set
- 05:29 because that's going to take time if you've activating every single worksheet,
- 05:32 but it gives you two different options.
- 05:34 You can go in and actually target specific sheets with your activation or
- 05:38 the whole workbook as a whole.
- 05:40 Another super important thing to be aware of when you now go to File >Save As,
- 05:44 you go to save this workbook, you need to change the file type to be a macro enabled
- 05:49 workbook or else you'll lose your macros,
- 05:51 this is kind of a key thing because it takes a different file type.
- 05:55 Keep in mind working with VBA is not a day one kind of thing.
- 05:58 This is a tricky technology we have an entire course on GoSkills dedicated to
- 06:02 actually helping you understand to learn, how to work through this stuff.
- 06:06 And I highly recommend if you are going to get in to this field that you should take
- 06:10 a course on VBA to really understand how it works and what's going on.
Lesson notes are only available for subscribers.