Automatic refresh of the pivot table data

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
I have a data entry sheet and a pivot table based on the data entry sheet. Is there a way to make the pivot table data refresh automatically based on some trigger (e.g., leaving the data entry sheet)?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dolphin

Board Regular
Joined
Nov 18, 2002
Messages
159
Try for a start:

Copy and paste your title in the search option at the top of the page.

There are a few examples.
 

cmwu

New Member
Joined
Feb 16, 2004
Messages
6
Place you cursor in the pivot table, click the pivtable menu...> table option, then, check the refresh on open...

Save you workbook and reopen it... it will automatically refresh...
 

cmwu

New Member
Joined
Feb 16, 2004
Messages
6
If you want to create an event driven macro:

In the VBA Project, select the worksheet that house the pivot table, then type the below event code....
Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

See whether it work or not!

rgds,
CM :wink:
 

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55

ADVERTISEMENT

Thank you!
 

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
To: CMWU

Your code worked perfectly.

Will your code work the same if I want a chart to refresh automatically? Or do I need to change it for a chart?

Thanks.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
steveski said:
I have a data entry sheet and a pivot table based on the data entry sheet. Is there a way to make the pivot table data refresh automatically based on some trigger (e.g., leaving the data entry sheet)?
Hi steveski:

How about using a Worksheet_Change event coupled with RefreshTable method as in ...
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Set pvtTable = Worksheets("Sheet1").Range("A11").PivotTable
    If Intersect(Target, [B2:C8]) Is Nothing Then Exit Sub
    pvtTable.RefreshTable
End Sub
in reference to the following illustration ...
y040216h1Book1.xls
ABCD
1DateNameNumbCalls
202/01/2004Joe5
302/01/2004Mary6
402/01/2004Bob4
502/02/2004Joe6
602/02/2004Bob6
702/03/2004Joe10
802/03/2004Joe1
9
10
11NameJoe
12
13SumofNumbCalls
14DateTotal
1502/01/20045
1602/02/20046
1702/03/200411
18GrandTotal22
Sheet1


I have used the trigger that the PivotTable is automatically refreshed whenever data in any of the cells B2:C8 changes.

Does it help?
 

steveski

Board Regular
Joined
Feb 9, 2004
Messages
55
Thank you Yogi, but I used the code CMWU posted and it worked great in a sheet, but I don't know how to get it to work in a stand-alone chart: (see below):

I have a data entry sheet, two pivot tables based on the data entry sheet, and two charts (not pivot charts) based on the pivot tables. One chart is located in a sheet, the other is a stand-alone chart. When I turn the file over to the user, the pivot tables will be hidden. I want both charts to automatically refresh when I click their tabs. For the chart located in the sheet, I have used the following code, which works great:

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

However, this code does not work if I use it with the stand-alone chart. Do I need to change something to make it work in the chart?

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top