You could create a temporary pivot table based on the connection to the
database. Then, programmatically set all the existing pivot tables to use the
same pivot cache.
For example, with the temporary pivot table on a worksheet named Temp:

'====================
Sub ChangeCache()
Dim ptNew As PivotTable
Dim ws As Worksheet
Dim pt As PivotTable

Set ptNew = Worksheets("Temp").PivotTables(1)
Debug.Print ptNew.CacheIndex

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.CacheIndex = ptNew.CacheIndex
Next pt
Next ws

End Sub
'==========================
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"bdub" wrote:

I have a pivot attached to a list on a tab in the same workbook. When I
click on the Change Data Source option, the "Use an External Data Source"
button is greyed out (I have a connection created already which I could
select).

How can I change the pivot from a local to an external data source?