Solved: Automated pivot table will not handle large data.

[Xrull]

Hi All,
I modified a code I found here:

http://www.vbaexpress.com/forum/showthread.php?t=32379

My problem is, I'm unable to get it to work if I use 70K rows.
The code works well with Excel 2003, but not with 2010.
I am unable to pinpoint the problem. I need some advice on this one.
[VBA]Sub Pivot()
' pivot Macro
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Dbase")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("PivotTbl")
Dim PTCache As PivotCache
Dim PRange As Range
For Each pt In PTOutput.PivotTables
pt.TableRange2.Clear
Next pt
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)

'/ I get an error at the line below. Run time error '13 Type Mismatch.

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="Pivot")

' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True

' Set up the row fields
pt.AddFields RowFields:=Array("Type")
' Set up the data fields
With pt.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With

pt.ManualUpdate = False
End Sub
[/VBA]
Regards,
Xrull

[mdmackillop]

Give this a try.

[vba] '/ I get an error at the line below. Run time error '13 Type Mismatch.

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=WSD.Name & "!" & PRange.Address)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
WSD.Name & "!" & PRange.Address, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=PTOutput.Cells(1, 1), TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14


' Create the pivot table
With PTOutput.PivotTables("PivotTable1").PivotFields("Type")
.Orientation = xlColumnField
.Position = 1
End With
[/vba]

[Xrull]

mdmackillop
mdmackillop,
Thanks for providing me with a solution.
I didn't know I'd have to do a little extra work by putting the "!" declared sheet name. My code works for 2003, and I think 2007.
I'm going to mark this as solved.
Regards,
Xrull

[anhlunnhaque]

mdmackillop,
Thanks for providing me with a solution.
I didn't know I'd have to do a little extra work by putting the "!" declared sheet name. My code works for 2003, and I think 2007.
I'm going to mark this as solved.
Regards,
Xrull

Do you have a working example file for demo? I got an error "Run-time error '91' Object variable or With block variable not set" at

pt.ManualUpdate = True

How and where do I set the pt ?