GoSkills
Help Sign up Share
Back to course

Automating Refresh

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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

Creating PivotCharts
05m:04s
Pre-release Considerations
06m:06s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement