How can I create a Pivot Table and name it using VBA? RRS feed

  • Question

  • I'm playing around with this small piece of code.

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Personnel&Facilities Detail!R3C1:R105279C21", Version:=xlPivotTableVersion14 _
            ).CreatePivotTable TableDestination:="Corporate Communications!R1C1", _
            TableName:="PivotTable9", DefaultVersion:=xlPivotTableVersion14

    I'm trying to loop through a range of cells and create a bunch of pivots based on some data sets that I have.  I have the loop working fine and I recorded a Macro that should do what I want.  The thing that I can't figure out is the naming convention of the pivot tables.  It seems to increment by 1 each time I turn on the Macro Recorder and click through the sequence of events.  I'm pretty sure the problem is here: 


    I tried to clear the cache of the pivot tables to reset the table name, but that didn't work. 

    Any idea what is wrong here?


    Thursday, August 4, 2016 7:00 PM


All replies