Microsoft Office Tutorials and References
In Depth Information
The macro is just getting started but I wanted to show you in slow motion what is taking place
under the radar when a new PivotTable is created. Actually, with the preceding line of code
executed, you could select one of those four cells and the PivotTable Field List would appear, inviting
you to drag fields to your desired location as shown in Figure 15-21.
If you want your PivotTable’s PivotCache to refresh automatically when a cell in your source list
changes, the following Worksheet_Change event will handle that. Note that the code uses the
PivotTable’s Index property for the first or only PivotTable on the worksheet to be refreshed.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“A1”).CurrentRegion) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub