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

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

--

Dave Peterson

Just read your thought. I'll take a look at what (external:=true) does.
I need to determine how the data range gets into the needed parameters necessary to prepare the
table. My guess is that the last "range" selected is the external.

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

I'm not sure what you mean, but external:=true is a way to tell excel that you
want to include the workbook name and worksheet name and row/column address.

Try this in a test macro:

With ActiveCell
MsgBox .Address(False, False) _
& vbLf & .Address(True, True) _
& vbLf & .Address(True, True, xlR1C1) _
& vbLf & .Address(external:=True)
End With

By using external:=true, I don't have to worry about building the string that
would make sure the address was what I wanted.


wrote:

Just read your thought. I'll take a look at what (external:=true) does.
I need to determine how the data range gets into the needed parameters necessary to prepare the
table. My guess is that the last "range" selected is the external.

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

--

Dave Peterson

Dave,

What a perfect way to explain a point!

EagleOne


Dave Peterson wrote:

I'm not sure what you mean, but external:=true is a way to tell excel that you
want to include the workbook name and worksheet name and row/column address.

Try this in a test macro:

With ActiveCell
MsgBox .Address(False, False) _
& vbLf & .Address(True, True) _
& vbLf & .Address(True, True, xlR1C1) _
& vbLf & .Address(external:=True)
End With

By using external:=true, I don't have to worry about building the string that
would make sure the address was what I wanted.


wrote:

Just read your thought. I'll take a look at what (external:=true) does.
I need to determine how the data range gets into the needed parameters necessary to prepare the
table. My guess is that the last "range" selected is the external.

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

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

IIRC, the pivottable code will work with either R1C1 reference style or A1
reference style--in fact, it can even be the range itself.

Did you try?
....,soucedata:=pivotrange, ...

But if you're going to use the .address, you'll want to include the
external:=true parm. That way you won't have to worry about the wrong worksheet
being used.


wrote:

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

--

Dave Peterson

For others where this may be a learning moment about PivotTables with VBA:

I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro.
Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with
the Macro-recorder on.

Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the
secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work
just fine, via his clever use of MsgBox.

Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went
right over my head.

Ultimately, "my" PT code ended up being Dave's 1st comment:

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

Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for
table placement on the same worksheet, notice the code:

TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, ....

which places the table in Row 1 but two columns to the right of the table data.

A round of applause for the MVP's who get -$0- for all of the help they give.

EagleOne


Dave Peterson wrote:

IIRC, the pivottable code will work with either R1C1 reference style or A1
reference style--in fact, it can even be the range itself.

Did you try?
...,soucedata:=pivotrange, ...

But if you're going to use the .address, you'll want to include the
external:=true parm. That way you won't have to worry about the wrong worksheet
being used.


wrote:

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

[Can not even post it right]


For others where this may be a learning moment about PivotTables with VBA:

I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro.
Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with
the Macro-recorder on.

Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the
secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work
just fine, via his clever use of MsgBox.

Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went
right over my head.

Ultimately, "my" PT code ended up being Dave's 1st comment:

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

Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for
table placement on the same worksheet, notice the code:

TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, ....

which places the table in Row 1 but two columns to the right of the table data.

A round of applause for the MVP's who get -$0- for all of the help they give.

EagleOne



Dave Peterson wrote:

IIRC, the pivottable code will work with either R1C1 reference style or A1
reference style--in fact, it can even be the range itself.

Did you try?
...,soucedata:=pivotrange, ...

But if you're going to use the .address, you'll want to include the
external:=true parm. That way you won't have to worry about the wrong worksheet
being used.


wrote:

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

This is one of those strings that I don't like to build:

TableDestination:="Pivot of Transactions!" & _
Cells(1, PivotRange.Columns.Count +2).address, _

I would have thought that this would fail. I would have guessed that you'd have
needed something like this:

TableDestination:="'Pivot of Transactions'!" & _
Cells(1, PivotRange.Columns.Count +2).address, _

But that's still not good for the lazy.

I would have used something like:

Dim TabDest as range

with worksheets("pivot of transactions")
set tabdest = .cells(1, PivotRange.Columns.Count +2)
end with

Then I could let excel/vba do the heavy lifting:

TableDestination:=tabdest.address(external:=true)
or simply
TableDestination:=tabdest


And did .Create really work for you--ahhh. You're using xl2007. .Create isn't
available in xl2003, so be careful if you have to share with people who use
earlier versions.



wrote:

[Can not even post it right]

For others where this may be a learning moment about PivotTables with VBA:

I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro.
Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with
the Macro-recorder on.

Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the
secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work
just fine, via his clever use of MsgBox.

Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went
right over my head.

Ultimately, "my" PT code ended up being Dave's 1st comment:

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

Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for
table placement on the same worksheet, notice the code:

TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, ....

which places the table in Row 1 but two columns to the right of the table data.

A round of applause for the MVP's who get -$0- for all of the help they give.

EagleOne

Dave Peterson wrote:

IIRC, the pivottable code will work with either R1C1 reference style or A1
reference style--in fact, it can even be the range itself.

Did you try?
...,soucedata:=pivotrange, ...

But if you're going to use the .address, you'll want to include the
external:=true parm. That way you won't have to worry about the wrong worksheet
being used.


wrote:

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

--

Dave Peterson