Microsoft Office Tutorials and References
In Depth Information
Try It
Suppose you have several PivotTables on many different worksheets and you want to be
confident that every PivotTable displays the current data from its respective source list. The following
Workbook_Open procedure will refresh every PivotTable in the workbook when the workbook opens:
Private Sub Workbook_Open()
Dim wks As Worksheet, PT As PivotTable
For Each wks In Worksheets
For Each PT In wks.PivotTables
Next PT
Next wks
End Sub
You can avoid looping through all your PivotTables by using VBA’s RefreshAll
method to refresh all PivotTables at once. The single line of code would be
ActiveWorkbook.RefreshAll . The RefreshAll method also refreshes all
external data ranges, such as web queries, for the specified workbook.
You might need to delete all the PivotTables on a worksheet. When you delete a PivotTable, what
you are really doing is clearing the cells that are occupied by the PivotTable. The following macro
will delete all the PivotTables on the active worksheet:
Sub DeleteAllPivotTables()
Dim objPT As PivotTable, iCount As Integer
For iCount = ActiveSheet.PivotTables.Count To 1 Step -1
Set objPT = ActiveSheet.PivotTables(iCount)
objPT.PivotSelect “”
Next iCount
End Sub
Try iT
In this lesson, you write a macro that adds a PivotChart to accompany an existing PivotTable. You
would like to create a PivotChart that will be located on the worksheet below the PivotTable.
lesson requirements
Your worksheet contains a list of source data, and you already have a PivotTable on your
worksheet. The worksheet is shown in Figure 15-22 before the PivotChart has been added. To get the
sample database files you can download Lesson 15 from the book’s website at .
Search JabSto ::

Custom Search