XL5: Macro Errors Changing Data Source to "Another PivotTable"

• Microsoft Excel for Windows, versions 5.0

SYMPTOMS

In Microsoft Excel version 5.0, the following error may occur when you attempt to change the data source for a pivot table to "Another PivotTable" in a Visual Basic, Applications Edition, macro:

   Run-time error '1004':

   PivotTableWizard method of Worksheet Class Failed.

CAUSE

This problem occurs if the pivot tables are in a different worksheet within the same workbook. This problem does not occur if both pivot tables are on the same worksheet.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

MORE INFORMATION

When you create a pivot table from data in another pivot table, both pivot tables are then linked to the same underlying source data. If you refresh one of the pivot tables, the other pivot table will automatically be refreshed.

Changing the SourceData argument of the PivotTableWizard method to a pivot table in another worksheet results in the problem described above.