Automating Refresh

Quick reference

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