Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotCache
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:=xlDatab ase, 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotCache
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:=xlDatab ase, _ SourceData:=strRng).CreatePivotTable TableDestination:="", _ TableName:="PivotTable3" '======================= T. wrote: 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:=xlDatab ase, 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 Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add an item to a PivotCache with VBA? | Excel Discussion (Misc queries) | |||
Pivotcache | Excel Programming | |||
Update PivotCache | Excel Programming | |||
Delete PivotCache | Excel Programming |