OK I believe I got your point by using:

PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property


"Returns a String value that represents the range reference in the language of the macro.

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)"

Thanks Dave



Dave Peterson wrote:

If pivotrange is a variable declared as a range, then how about:

...,soucedata:=pivotrange.address(external:=true), ...



wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _
"Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _
"PivotOfTrans", DefaultVersion:=xlPivotTableVersion10

Curious why R1C1 style formula references?

Also, why does a range reference like PivotRange
not work i.e.:

Dim myRange as Range
PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown))

AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _
"Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _
PivotRange.Columns.Count +1) , TableName:= _
"PivotOfTrans", DefaultVersion:=xlPivotTableVersion10

TIA EagleONe