Variable Address in Pivot Table VBA code

[Ron Luzius]

How can I get a variable range in this VBA code instead of this hard-coded
range (R1C1:R1345C25)?

Every time I run my Excel macro, I will have a different amount of rows to
include in the PivotTable code.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Total Open Discrepancies'!R1C1:R1345C25").CreatePivotTable
TableDestination _
:="", TableName:="PivotTable1"

 

[Per Jessen]

Hi

This will use the last row in Column A with data:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"'Total Open Discrepancies'!R1C1:R" & LastRow &
"C25").CreatePivotTable TableDestination _
:="", TableName:="PivotTable1"

Regards,
Per

 

[Ithyan]

Hi Jessen,

I also have a similar problem i want to change the below line in the code
SourceData:= "Sheet1!R1C1:R49027C35"

How can we do this?
Please suggest.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R49027C35").CreatePivotTable TableDestination:="", _
TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10

 

[Ithyan]

It's working.
Thank You.

Hi

This will use the last row in Column A with data:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"'Total Open Discrepancies'!R1C1:R" & LastRow &
"C25").CreatePivotTable TableDestination _
:="", TableName:="PivotTable1"

Regards,
Per

 

[ilter]

Hi Jessen,

Thanks.