Case Study Excel

Pivot tables and VBA took a radical turn in Excel 2000. In Excel 2000, Microsoft introduced the Pivotcache object.This object allows you to define one Pivotcache and then build many pivot reports from the Pivotcache.

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,then you should be aware of how pivot tables were created in Excel 97.

In Excel 97,you would use the PivotTableWizard method. By way of illustration, I will show the code for building a simple pivot table showing Revenue by Region and Product.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()

1 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("Pivot Table")

1 Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

1 Define input area and set up a pivot cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

' Create pivot table using PivotTableWizard

0 0

Post a comment