Macro 62: Create a Backwards- Compatible PivotTable
If you are still using Excel 2003, you may know about the compatibility headaches that come with
PivotTables between Excel 2003 and later versions. As you can imagine, the extraordinary increases
in PivotTable limitations lead to some serious compatibility questions. For instance, later versions of
Excel PivotTables can have more than 16,384 column fields and more than 1,000,000 unique data
items. Excel 2003 can have only 256 column fields and 32,500 unique data items.
To solve these compatibility issues, Microsoft has initiated the concept of Compatibility mode.
Compatibility mode is a state that Excel automatically enters when opening an xls file. When Excel is
in Compatibility mode, it artificially takes on the limitations of Excel 2003. This means while you are
working with an xls file, you cannot exceed any of the Excel 2003 PivotTable limitations, allowing you
(as a user of Excel 2007 or 2010) to create PivotTables that work with Excel 2003.
If you are not in Compatibility mode (meaning you are working with an xlsx or xlsm file) and you
create a PivotTable, the PivotTable object turns into a hard table when opened in Excel 2003. That is to
say, PivotTables that are created in xlsx or xlsm files are destroyed when opened in Excel 2003.
To avoid this fiasco manually, Excel 2007 and 2010 users must go through these steps:
1. Create a blank workbook.
2. Save the file as an xls file.
3. Close the file.
4. Open it up again.
5. Start creating the PivotTable.
This is enough to drive you up the wall if you’ve got to do this every day.
An alternative is to use a macro that automatically starts a PivotTable in Table in the Excel 2003 ver-
sion — even if you are not in Compatibility mode!
How it works
If you record a macro while creating a PivotTable in Excel 2007 or Excel 2010, the macro recorder gen-
erates the code to create your PivotTable. This code has several arguments in it. One of the arguments
is the Version property. As the name implies, the Version property specifies the version of Excel
the PivotTable was created in. The nifty thing is that you can change the Version in the code to
force Excel to create a PivotTable that will work with Excel 2003.
Here is a listing of the different versions you can specify:
➤ xlPivotTableVersion2000 – Excel 2000
➤ xlPivotTableVersion10 – Excel 2002
➤ xlPivotTableVersion11 – Excel 2003
➤ xlPivotTableVersion12 – Excel 2007
➤ xlPivotTableVersion14 – Excel 2010
Here is an example of a macro that starts a PivotTable using Range(“A3:N86”) on Sheet1 as the
source data.
Note that we changed the Version and DefaultVersion properties to xlPivotTable
Version11. This ensures that the PivotTable starts off as one that will work in Excel 2003.
No need to save your workbook as an .xls file first or to be in Compatibility mode. You can use a sim-
ple macro like this (just change the source data range) to create a PivotTable that will automatically
work with Excel 2003.
Sub Macro62()
Dim SourceRange As Range
Set SourceRange = Sheets(“Sheet1”).Range(“A3:N86”)
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceRange, _
Version:=xlPivotTableVersion11).CreatePivotTable _
TableDestination:=””, _
TableName:=””, _
DefaultVersion:=xlPivotTableVersion11
End Sub
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
