Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart

'Delete all pivot tables and pivot charts that might be on the worksheet,
'to start off with a clean worksheet for this demonstration.
If ActiveSheet.ChartObjects.Count > 0 Then ActiveSheet.ChartObjects.Delete
Dim objPT As PivotTable, iCount As Integer
For iCount = ActiveSheet.PivotTables.Count To 1 Step -1
Set objPT = ActiveSheet.PivotTables(iCount)
objPT.PivotSelect ""
Selection.Clear
Next iCount

'It is a good practice to deselect any embedded object that may be selected.
Application.Goto Range("A1"), 1

'This tells the user about the demonstration process, starting from the beginning.
MsgBox _
"Any pivot table(s) and/or pivot chart(s) that were" & vbCrLf & _
"previously on this worksheet have been deleted, so" & vbCrLf & _
"this demonstration can be shown from the start." & vbCrLf & vbCrLf & _
"This macro in Module1 contains bonus VBA code that" & vbCrLf & _
"shows how to identify a pivot table's range location," & vbCrLf & _
"and how to delete all pivot tables and/or pivot charts" & vbCrLf & _
"on a worksheet." & vbCrLf & vbCrLf & _
"Click OK for the step-by-step demonstration.", 64, _
"Step by step anatomy of a pivot table and pivot chart."

'Declare and define the necessary variables.
Dim lngDestinationColumn As Long, strPivotCache As String
Dim rngChartReportRange As Range, chtChartReport As ChartObject
Dim pvtLastRow As Long, pvtLastColumn As Long, LastRowSourceRange As Long
lngDestinationColumn = Range("B6").CurrentRegion.Columns.Count + 3
strPivotCache = Cells(8, lngDestinationColumn).Resize(2, 2).Address(0, 0)
LastRowSourceRange = Cells(Rows.Count, 2).End(xlUp).Row

'Step 1 - Create the pivot cache.
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Range("B6").CurrentRegion).CreatePivotTable _
TableDestination:="R8C" & lngDestinationColumn
Application.Goto Range("A1"), 1
MsgBox "A new pivot cache has been created in" & vbCrLf & _
"range " & strPivotCache & ". It looks like four empty cells" & vbCrLf & _
"with a thin black border around them." & vbCrLf & vbCrLf & _
"Click OK for Step 2.", , "Step 1 of 11."

'Open a With structure for the pivot table.
With ActiveSheet.PivotTables(1)

'Step 2 - First (outer) row field.
With .PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
MsgBox "The first (outer) row field was added" & vbCrLf & _
"to the pivot cache, for ''Region''." & vbCrLf & vbCrLf & _
"Click OK for Step 3.", , "Step 2 of 11."

'Step 3 - Second (inner) row field.
With .PivotFields("Store ID")
.Orientation = xlRowField
.Position = 2
End With
MsgBox "The second (inner) row field was added" & vbCrLf & _
"to the pivot cache, for ''Store ID''." & vbCrLf & vbCrLf & _
"Click OK for Step 4.", , "Step 3 of 11."

'Step 4 - Column field.
With .PivotFields("When")
.Orientation = xlColumnField
.Position = 1
End With
MsgBox "A column field was added to the" & vbCrLf & _
"pivot cache, for ''When''." & vbCrLf & vbCrLf & _
"Click OK for Step 5.", , "Step 4 of 11."

'Step 5 - Filters area.
With .PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
MsgBox "This step shows how to add a" & vbCrLf & _
"Filters area, and a field to it." & vbCrLf & _
"The ''Item'' field was added there." & vbCrLf & vbCrLf & _
"Click OK for Step 6.", , "Step 5 of 11."

'Step 6 - Revenue in the Values field.
.AddDataField ActiveSheet.PivotTables(1).PivotFields("Revenue"), "Sum of Amount", xlSum
MsgBox "The ''Revenue'' field was added to the" & vbCrLf & _
"pivot table's Values section." & vbCrLf & vbCrLf & _
"Click OK for Step 7.", , "Step 6 of 11."

'Step 7 - Format numbers in the Values field as Accounting.
.PivotFields("Sum of Amount").NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Columns(9).AutoFit
MsgBox "The ''Revenue'' field was formatted for Accounting." & vbCrLf & vbCrLf & _
"Click OK for Step 8.", , "Step 7 of 11."

'Step 8 - Define the pivot table's last row and last column so you can know what the row
' 2 rows below that will be, and how far to extend the pivot chart width-wise.
With .TableRange1
pvtLastRow = .Rows.Count + .Row - 1
pvtLastColumn = .Columns.Count + .Column - 1
End With
MsgBox _
"The last row of the pivot table is " & pvtLastRow & "." & vbCrLf & _
"The last column of the pivot table is " & pvtLastColumn & "," & vbCrLf & _
"which is column O." & vbCrLf & vbCrLf & _
"This information will be used in Step 9" & vbCrLf & _
"to define the range where the pivot" & vbCrLf & _
"chart will ultimately be placed." & vbCrLf & vbCrLf & _
"Click OK for Step 9.", , "Step 8 of 11."

'Step 9 - Create the pivot chart in 5 substeps.
'Step 9a - Set the objPT variable for the one (and only in this example) pivot table.
Set objPT = ActiveSheet.PivotTables(1)
'Step 9b - Select the pivot table.
objPT.PivotSelect ""
'Step 9c - Add the pivot chart.
Charts.Add
'Step 9d - Place the pivot chart on the PivotTable's worksheet.
ActiveChart.Location Where:=xlLocationAsObject, Name:=objPT.Parent.Name
'Step 9e - Position the pivot chart to cover a specific range of cells, in this example
' from the row that is 2 rows below the last row of the pivot table
' (which will be row 25), and from the column that is 2 columns to the right
' of the source range's right-most column (which will be column I).
' That is, the pivot table and its pivot chart will occupy the same columns, and
' the pivot chart's last row will be the same last row as its source data range.
Set chtChartReport = ActiveChart.Parent
Set rngChartReportRange = Range(Cells(pvtLastRow + 2, lngDestinationColumn), Cells(LastRowSourceRange, pvtLastColumn))
With chtChartReport
.Left = rngChartReportRange.Left
.Width = rngChartReportRange.Width
.Top = rngChartReportRange.Top
.Height = rngChartReportRange.Height
End With
MsgBox _
"The pivot chart is created and is located" & vbCrLf & _
"exactly where you wanted it in this example." & vbCrLf & vbCrLf & _
"Notice at this moment that the pivot chart" & vbCrLf & _
"is selected. Click OK for Step 10.", , "Step 9 of 11."

'Close the With structure for the pivot table.
End With

'Step 10 - Deselect the pivot chart by going to cell A1.
Application.Goto Range("A1"), True
MsgBox _
"Deselect the pivot chart that you just created." & vbCrLf & _
"A good, user-friendly method is to go to the top" & vbCrLf & _
"of the worksheet, and select cell A1." & vbCrLf & vbCrLf & _
"Click OK for final Step 11.", , "Step 10 of 11."

'Step 11 - Release system memory from the Set object variables.
Set objPT = Nothing
Set chtChartReport = Nothing
Set rngChartReportRange = Nothing
MsgBox _
"The object variables' memory has been cleared." & vbCrLf & _
"The programming process is completed!", , "Step 11 of 11."