Source data of a PIvot Table

Question

I am creating a VBA code to create a Pivot Table, but my source of data change of length each time I want to run the code, so I would like to reference it according to a variable.

Dim SrcData As String

SrcData = "Range(Cells(4,1),Cells(lRowPTF,lColPTF))"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SrcData, Version:=6).CreatePivotTable _
        TableDestination:="PT!R3C1", TableName:="PivotTable3", DefaultVersion _:=6

The pivot table is not created.

create a namedrange with this formula =OFFSET(SHEETNAME!A1,0,0,COUNTA(SHEETNAME!A:A),COUNTA(SHEETNAME!1:1)) this way you will have a dynamic source which will grow with your data. — Damian

Shai Rado Shai Rado · Accepted Answer · 2019-09-03T13:08:13

Try modifying to the code below:

Dim SrcRng As Range
Dim SrcData As String

Set SrcRng = Range(Cells(4, 1), Cells(lRowPTF, lColPTF))
SrcData = SrcRng.Address(False, False, xlA1)

Source data of a PIvot Table

1 Answers