Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Address in Pivot Table VBA code
Hi Jessen,
Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't set pivot table object variable | Excel Programming | |||
Pivot Table Range Address, Row & Column numbers | Excel Programming | |||
how do I hard code a variable range for a pivot table in vba? | Excel Programming | |||
VBA Code for a pivot table to open database and to reference table current page | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |