Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 703
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Variable Address in Pivot Table VBA code

Hi Jessen,

Thanks.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't set pivot table object variable Jeff S[_2_] Excel Programming 0 February 13th 07 11:53 PM
Pivot Table Range Address, Row & Column numbers Edmund Excel Programming 1 October 19th 06 07:56 AM
how do I hard code a variable range for a pivot table in vba? havocdragon Excel Programming 1 June 29th 05 10:21 PM
VBA Code for a pivot table to open database and to reference table current page Pete Straman Straman via OfficeKB.com Excel Programming 0 February 21st 05 04:57 AM
Pivot Table - Object variable or with block variable not set? George Nicholson[_2_] Excel Programming 1 April 16th 04 09:12 PM


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"