PivotCache

[Guest]

I am using MS Dynamics GP SmartList Export Solution. The code to create a
pivot chart when I export the SmartList includes this:

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

I want to replace the second line: "Sheet1!R1C1:R322C14" with variables
that will change depending on the row number in the SmartList I am exporting.
The number of rows will vary from day to day depending on the number of open
transactions.

The range of the data for the pivot chart will always go from A1 to Last
Cell. I can capture the address of the last cell, but I can't figure out how
to replace the hard coded range with the variables. Right now I am setting
A1 as the value of a variable called HomeCell and whatever the last cell
address is for the value of a variable called CellRef. However when I change
the PivotCache to read:

"Sheet1!HomeCell, CellRef"

I get an error. I have seen this done before so I know it is possible, but
I cannot remember the syntax. Can anyone help?

Thanks.

T.

 

[Debra Dalgleish]

You could use the address of the current region, e.g.:

'=======================
Dim rngSource As Range
Dim strRng As String
Set rngSource = Worksheets("Sheet1").Range("A1").CurrentRegion
strRng = "Sheet1!" & rngSource.Address

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=strRng).CreatePivotTable TableDestination:="", _
TableName:="PivotTable3"
'=======================