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

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
================

AABob wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com