Automatic refresh of the pivot table data

[steveski]

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)?

 

[dolphin]

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]

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]

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]

Thank you!

 

[steveski]

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]

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

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
	A	B	C	D
1	Date	Name	NumbCalls
2	02/01/2004	Joe	5
3	02/01/2004	Mary	6
4	02/01/2004	Bob	4
5	02/02/2004	Joe	6
6	02/02/2004	Bob	6
7	02/03/2004	Joe	10
8	02/03/2004	Joe	1
9
10
11	Name	Joe
12
13	SumofNumbCalls
14	Date	Total
15	02/01/2004	5
16	02/02/2004	6
17	02/03/2004	11
18	GrandTotal	22
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]

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.