PivotTableWizard Method Fails If Active Cell Not In Table

• Microsoft Excel for Windows version 5.x
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, if you use the PivotTableWizard method in Visual Basic for Applications to re-create an existing PivotTable, the active cell must be in the existing PivotTable, or you receive the following error message:

   Run-time error '1004':
   PivotTableWizard method of Worksheet class failed

RESOLUTION

To resolve this problem, make sure that the active cell is in the specified PivotTable before you run the PivotTableWizard method on it. You can use the following Visual Basic for Applications code to place the active cursor for you.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Visual Basic Macro Code Example

   Sub SelectCell()
      Worksheets(1).PivotTables(1).TableRange2.Resize(1, 1).Select
      'This selects the upper left-hand corner cell of the first
      'PivotTable on the first sheet.
   End Sub

MORE INFORMATION

The only way to change ODBC connection information for a PivotTable made from external data is to re-create the table. If you want to do this programmatically, it is convenient to use the pre-existing PivotTable with the PivotTableWizard method to re-create it because the underlying field structure of the Table will not be modified. (Note that if you simply create a new table from scratch, you create a table with no fields, and this requires quite a bit of extra code to build.)

To change ODBC connection information by using a pre-existing PivotTable with the PivotTableWizard method to re-create it, use the same name in the Name argument of the PivotTableWizard method as that of an existing PivotTable. For this to work, however, the active cell must be in the existing PivotTable or you receive the error message described at the beginning of this article.