Using Debra's template I found success with XL 2003

AccountFileBook.PivotCaches.Add(SourceType:=xlData base, 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




Dave Peterson wrote:

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