Can't change Pivot Table data source
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 |
Can't change Pivot Table data source
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 |
All times are GMT +1. The time now is 02:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com