Why R1C1 range parameters in VBA Pivot Table setup/

[EagleOne]

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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]

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

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

 

[EagleOne]

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]

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.

 

[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

 

[EagleOne]

Dave,

What a perfect way to explain a point!

EagleOne

 

[Dave Peterson]

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.

 

[Dave Peterson]

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.

[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:=xlDatabase, 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

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.

 

[Dave Peterson]

If you're gonna make sure your code works in every version that will use it, it
may be best to develop in the earliest version of excel. Then test it in newer
versions.

Usually, they'll be fewer things to change.

Thank you so much for the compatibility issue as that is a major concern.

Gee! Dave, why the issue over "'" vs "" who could possibly notice the difference?
These dammmmed computers.

And also, TableDestination:=tabdest. What can I say?

Thank you!

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.

[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:=xlDatabase, 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


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.


(e-mail address removed) 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


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

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



(e-mail address removed) wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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]

And now you say Q@#Qing ^@#$ing VBA!

<vbg>

Dave,

From an" Old-fart" - 64 like me, thanks for your insight and knowledge.

VBA is, absolutely, a necessary "evil" to make XL life more efficient in an age when it seems that
few want to work very hard. Three years ago, if someone said VBA, I would have said what?

EagleOne

<<snipped>>

 

[EagleOne]

2007 (compatibility Mode 2003)

Dave,

The macro recorder records exactly:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

If I copy that very code into a Sub(), I get an error #5
"Invalid procedure call or argument"

I am missing something obvious.

I have activated the sheet via AccountFileBook.Activate

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.

[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:=xlDatabase, 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

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.


(e-mail address removed) 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


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

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



(e-mail address removed) wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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]

Maybe someone who uses xl2007 will chime in.

2007 (compatibility Mode 2003)

Dave,

The macro recorder records exactly:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

If I copy that very code into a Sub(), I get an error #5
"Invalid procedure call or argument"

I am missing something obvious.

I have activated the sheet via AccountFileBook.Activate

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.

[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:=xlDatabase, 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


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.


(e-mail address removed) 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


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

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



(e-mail address removed) wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

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

TIA EagleONe

 

[EagleOne]

Dave, I got this from Google Search.

Debra Dalgleish Jan 15, 2007 gave an answer to the same issue:



"Assuming the worksheets all have data starting in the top left corner,
you could use code similar to the following, substituting your field names: "

'===================
Sub CreatePTs()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsPT As Worksheet
Dim strName As String
Dim strNamePT As String


strName = "Pivot_"
strNamePT = "PT_"


Set wb = ActiveWorkbook


For Each ws In wb.Worksheets
If Left(ws.Name, 6) <> strName Then
wb.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=ws.Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="", TableName:=strNamePT & ws.Name
Set wsPT = ActiveSheet
With wsPT
.Name = strName & Left(ws.Name, 25)
.PivotTableWizard TableDestination:=.Range("B4")
With .PivotTables(1)
.AddFields RowFields:="Year"
.PivotFields("Total").Orientation = xlDataField
End With
End With
End If
Next ws


End Sub


If anyone knows why the automation error though?

Maybe someone who uses xl2007 will chime in.

2007 (compatibility Mode 2003)

Dave,

The macro recorder records exactly:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

If I copy that very code into a Sub(), I get an error #5
"Invalid procedure call or argument"

I am missing something obvious.

I have activated the sheet via AccountFileBook.Activate

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.



(e-mail address removed) 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:=xlDatabase, 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


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.


(e-mail address removed) 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


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

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



(e-mail address removed) wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

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

TIA EagleONe

 

[EagleOne]

Using Debra's template I found success with XL 2003

AccountFileBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Pivot of Transactions").Range("A1").CurrentRegion). _
CreatePivotTable TableDestination:="'Pivot of Transactions'!R1C6", _
TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10

Notice notice the change after the SourceData:=
From: "Pivot of Transactions!R1C1:R2458C4"
To: Sheets("Pivot of Transactions").Range("A1").CurrentRegion)

I also played with the 2003 vs 2007 issue (to no avail):
Pivot of Transactions!
vs
'Pivot of Transactions'!

So the above works for XL 2003. It appears to be something related to how the data source is
presented.

I also noted the KB item but am not sure of its relevance.

http://support.microsoft.com/kb/263498/en-us

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.

[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:=xlDatabase, 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

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.


(e-mail address removed) 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


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

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



(e-mail address removed) wrote:

2007 (compatibility mode)

AccountFileBook.PivotCaches.Create(SourceType:=xlDatabase, 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("A11"), Range("A11").End(xlDown))

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

TIA EagleONe