CODEWITHSUNDEEP
excelvbapivot-table
user11993592
user11993592

Reputation: 25

Source data of a PIvot Table

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.

Upvotes: 1

Views: 98

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

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)

Upvotes: 1

Related Questions