I'm having trouble programatically creating a pivot table. If I step through
the code below it works, but if I run it, it falls over on the Set PT line.
Can anyone see a problem with my code?
Thanks
Rob

Sub MakePivot(ReportName As String, DataAddress As String, ReportAddress As
Range)

Dim PTcache As PivotCache
Dim PT

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=DataAddress)
Set PT = PTcache.CreatePivotTable(TableDestination:=ReportA ddress,
TableName:=ReportName)

I usually find it easiest to cheat and record the creation of a pivot
table, then tweak the pivottablewizard code... it's inelegant, but it
saves a lot of time!

Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=545434

I found the solution. If anyone's interested then here's what was happening:

The DataAddress parameter being passed to the function did not include the
sheet name. (ie. it was something like "A1:H50"). When stepping through,
excel had time to resolve this address as referring to the active sheet, but
when Running, it did not.

Regards
Rob




"Rob" wrote:

I'm having trouble programatically creating a pivot table. If I step through
the code below it works, but if I run it, it falls over on the Set PT line.
Can anyone see a problem with my code?
Thanks
Rob

Sub MakePivot(ReportName As String, DataAddress As String, ReportAddress As
Range)

Dim PTcache As PivotCache
Dim PT

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:=DataAddress)
Set PT = PTcache.CreatePivotTable(TableDestination:=ReportA ddress,
TableName:=ReportName)