Preserving Named Tables as Pivot Table data sources after copying to new workbook (Excel 2010) RRS feed

  • Question

  • Have 2010 Excel Workbook with Named Tables and child Pivot Tables and several other support sheets.  I want to make a 'working' file --  with only copies of these Named Tables and Pivot Table structures, and then I would populate the copied Tables with the 'real data' and crunch the numbers I need in this new working file.

    I need to use VBA for this transformation (which I've tried).

    Problem -- Upon copying the Named Table [Table1] and Pivot Tables [Pivot11] and [Pivot12] to the new stand-alone file, WORKING.xlsm, the Pivot Tables only recognize the Tables in the source file (I.e. the data source for Pivot11 is C:\...\'SOURCE.xlsm'!Table1), and not simply Table1 in the new stand-alone file.

    Longhand changing of the Data Source for each Pivot Table [Table1] from 'C:\...\SOURCE.xlsm'!Table1 to simply Table1 seems to be disallowed from all angles.

    Anyone have clean VBA code that will copy the parent Named Table and its child Pivot Tables to an external .xlsm file without the absolute links back to the original source file ?

    Tuesday, January 13, 2015 1:59 AM


  • Hi Jon,

    How do you change the data source?

    Base on my test, we can change the data source by using this code below:

    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "[path]!Table4" _
            , Version:=xlPivotTableVersion15)

    To copy and paste the Named Table and Pivot Tables, we could use Range.Copy and Range.PasteSpecial method.

    To check whether the Pivot Tables parent is specify Named Table, we could check PivotTable.SourceData property.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 14, 2015 6:48 AM