Morning,

We currently have some Excel 2010 spreadsheets that have pivot tables in them.

The pivot tables are based on a ODBC connection to a database. We have written some VBA that will extract the source data required from another database to a worksheet and execute some processes on it.

When I try to alter the existing pivot table's data source and change it from a External Datasource Connection to a table range in the workbook I am unable to do so. 'Select a table or range' option is greyed out.

Can someone tell me if there is a way in VBA to change the data source fron existing Excel 2010 Pivot Table from an external datasource to a named range on a sheet.

Thanks
Chris