Hi steveski: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)?
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
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 |