People who use Pivot Tables regularly knows this issue very well.

….i.e. manually refreshing pivot tables in your workbooks.

It’s perfectly alright to use the manual method if you have one or two pivot tables in your reports.

…but what if you have 5 to 10 pivot tables and then you will need to refresh all of them regularly..?

Well.. that’s a boring job.

So with that in mind, I have written this tutorial to help you build your own Pivot table refresh macro to automate the process.

Steps to write Pivot Table Refresh Macro

Step #1

Create a new excel workbook and save as “Macro Enabled workbook/Excel Binary workbook” file type.

Step #2

Head over to “Developer” tab and click on “Visual Basic”

In case if you have don’t have “Developer” tab in your workbook [it will be disabled by default] please refer the following tutorial to enable the Developer tab.

Developer tab in excel

Step #3

Rightclick on This Workbook → Insert → Module

You will most likely have “Module 1” under Modules

Step #4

Click on your newly created Module and start writing your macro code like below.

Sub Refresh_All_PivotTables()

   Dim WSheet As Worksheet
   Dim PVT As PivotTable

     For Each WSheet In ActiveWorkbook.Worksheets
         For Each PVT In WSheet.PivotTables
           PVT.RefreshTable
           PVT.Update
        Next
   Next

 End Sub

Now you have required code in your macro to refresh all the Pivot tables, it’s time to learn how to use this macro.

Step #5

Open your report with pivot tables, also open your pivot refresh macro.
[If you see any warning message, go ahead and click on “Enable this content”]

Step #6

On your report click on → Alt+F8 → Select macro name → Click on Run

That’s all… !!!

How to run pivot table refresh macro

Now go back to each and every pivot table in your report and check. I’m sure they would have refreshed with the latest information.

It’s cool, isn’t it..?

…but hang on, there is a problem.

This macro will not extend pivot table data range instead, it will just refresh all the pivot tables in a workbook.

Just imagine, what will happen if you add/append new data to your existing data sheet..?

Obviously, your pivot tables will not have most recent data.

How to extend Pivot table range automatically

The best way that I could think of is by converting your data range into a Table.

By doing so, your data range will be automatically updated as and when you have new data added/appended.

but… how do you do that ..?

well, it’s very simple, just with a click you can do that.

Steps to convert Excel range to a Table

Step #1

Select entire data in your data sheet.

Step #2

Head over to Insert menu → Click on Table

Alternatively, you can also use Ctl+T shortcut key for the same.

Step #3

Now to go “Design” tab → under properties → Change table name (by default it will be called as “Table1”), I’d call it as a “my_data”

Changing Table name is not mandatory but it’s good to have descriptive tables names for quick reconciliation.

Step #4

In general, when you paste new data under a table, the data will be automatically converted into a Table.

But.. there are few cases where it might not…

(I don’t know the exact reason, but it does happen sometimes)

If not…. then you can do it quickly by going to → Design → select ‘Resize table’ under ‘properties’ → select range.

Here is a video guide:

You can download Pivot Refresh Macro from here.

Conclusion

If you have one or two pivot tables it’s ok to refresh them manually.. but if you more than that it’s good to go with Pivot Table Refresh macro.

Trust me with the help of this macro you can save a lot of time.

Start using this macro from today and let me know if you have any issues or suggestions.