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. — user6585761
– user6585761, Commented Mar 12, 2020 at 21:03

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

0

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

answered Mar 12, 2020 at 20:52

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

vba pivot table Excel 2016

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related