Automatically Refreshing a PivotTable Report

Pivot table is an excellent feature of Excel that lets user make summary report from various perspective/angle. There are two ways we can refresh pivot table A) Manual Refresh; B) Automatic Refresh.

We can click on Refresh at any time to update the workbook data for the PivotTables. Anytime, we can refresh the data for PivotTables connected to external data, such as a database (SQL Server, Oracle, Access, or other), Analysis Services cube, data feed, and many other sources. We will learn both steps in this tutorial.

Let us first understand Manual Refresh to refresh PivotTable manually. Follow the below mentioned steps:

  • Click on any cell in the PivotTable to show the PivotTable Tools on the ribbon.
  • PivotTable Tools
  • Click on Analyze tab > Refresh, or press Alt+F5

 

sample

 

Note: To update all of the existing PivotTables at once in your workbook, click Analyze > Refresh arrow > Refresh All.

To automatically refresh the data in a PivotTable Report:

• Step 1: Automatically update the source data range Name
• Step 2: Add a VBA Event to automatically refresh the PivotTable report
1. Press Alt+F11, and then double-click the sheet name in the VBAProject pane.
2. From the left dropdown list above the Module sheet, select Worksheet, and from the right dropdown list, select Activate.
3. Copy the code from the Macro Event Activate in the screenshot.
4. Press Ctrl+S to save the workbook, and then press Alt+F4 to close the VBE. The

PivotTable report is refreshed automatically upon selecting the sheet that contains it.

Note:
To find the PivotTable report name (in this example, PivotTable1), select a cell in the PivotTable report, right click, and then select Table Options from the shortcut menu.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube