Can't change Pivot Table data source

[AABob]

Excel 2007. My pivot table is currently pointing to an MS Access
database. I want to change it to point to a worksheet within the
same workbook. When I go to the Change Data Source area, the option
to choose a local range is greyed out. The only thing I can do is
change to another external data connection. Any advice would be
appreciated.

Thanks,
AABob

 

[Debra Dalgleish]

You could create a temporary pivot table from the Excel data. Don't
waste any time formatting it, just create it and add a field or two.

Then, use programming to change the old pivot table to use the new pivot
cache. In the sample code below, substitute your sheet names.

After it's changed, you can delete the temporary pivot table.

'======================
Sub SwitchPC()
Dim pt As PivotTable
Dim ptTemp As PivotTable

Set pt = Worksheets("OldPivot").PivotTables(1)
Set ptTemp = Worksheets("TempPivot").PivotTables(1)

pt.CacheIndex = ptTemp.CacheIndex
End Sub
================

 

[Titan74]

You could use:
Press ALT, D, P, P

And go into the Pivot Table wizard of EXCEL XP, then press back n the wizard until you get to change the source data

Esteban