Officially, Microsoft quit supporting Excel 97 a few years ago. But, in practical terms, there are still many companies using Excel 97. If you need your code to work on a legacy platform, you should be aware of how pivot tables were created in Excel 97.
In Excel 97, you would use the PivotTableWizard method. Take a look at the code for building a simple pivot table showing revenue by region and line of business. Where current code uses two steps (add a PivotCache and then use CreatePivotTable), Excel 97 would use just one step, using the PivotTableWizard method to create the table:
Sub PivotExcel97Compatible()
' Pivot Table Code for Excel 97 Users
Dim WSD As Worksheet
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PivotTable")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
' Create pivot table using PivotTableWizard
Set PT = WSD.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=PRange.Address, _
TableDestination:="R2C13", TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Region", ColumnFields:="Line of Business"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0,K"
.Name = "Total Revenue"
End With
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
by updated