Variable Address in Pivot Table VBA code
 

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:=xlDatab ase, SourceData:= _
"'Total Open Discrepancies'!R1C1:R1345C25").CreatePivotTable
TableDestination _
:="", TableName:="PivotTable1"

Variable Address in Pivot Table VBA code
 

Hi

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

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

Regards,
Per

On 23 Sep., 20:53, "Ron Luzius" wrote:
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:=xlDatab ase, SourceData:= _
* * * * "'Total Open Discrepancies'!R1C1:R1345C25").CreatePivotTable
TableDestination _
* * * * :="", TableName:="PivotTable1"

Variable Address in Pivot Table VBA code
 

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:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R49027C35").CreatePivotTable TableDestination:="", _
TableName:="PivotTable5", DefaultVersion:=xlPivotTableVersion10




"Per Jessen" wrote:

Hi

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

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

Regards,
Per

On 23 Sep., 20:53, "Ron Luzius" wrote:
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:=xlDatab ase, SourceData:= _
"'Total Open Discrepancies'!R1C1:R1345C25").CreatePivotTable
TableDestination _
:="", TableName:="PivotTable1"

Variable Address in Pivot Table VBA code
 

It's working.
Thank You.

"Per Jessen" wrote:

Hi

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

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

Regards,
Per

On 23 Sep., 20:53, "Ron Luzius" wrote:
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:=xlDatab ase, SourceData:= _
"'Total Open Discrepancies'!R1C1:R1345C25").CreatePivotTable
TableDestination _
:="", TableName:="PivotTable1"

Variable Address in Pivot Table VBA code
 

Hi Jessen,

Thanks.