vba pivot table Excel 2016

Question

Did the vba syntax for pivot table change, in Excel 2016? This line, first in a block that creates a pivot table, was working under Excel 2010, but does not work now, under Excel 2016/ Windows 10:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & lnglastrow & "C" & lnglastcol & "", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

You'll get an error if a pivottable already exists at the destination, or the worksheet already contains a pivottable with the same name. In any case, best to split it up, as Tim has shown you. — Domenic

Tim Williams Tim Williams · Accepted Answer · 2020-03-12T20:52:45

Typically it works if you split up that line into two steps

Dim pc as pivotcache, pt as pivottable

Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:= "Sheet1!R1C1:R" & lnglastrow & "C" & lnglastcol, _ 
          Version:=xlPivotTableVersion14)

Set pt = pc.CreatePivotTable(TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", _
                               DefaultVersion :=xlPivotTableVersion14

vba pivot table Excel 2016

1 Answers