Pivot table

F

Fei

Hi,
I am trying to make a pivot table and my codes are following:

Sub PivotTable()
Dim pt As PivotTable
Dim PRange As Range
Dim PTCache As PivotCache
Dim FinalRow As Long
'Dim FinalCol As Long
Set WSD = Worksheets("Data")


Set OutputPivot = Worksheets("Output")

FinalRow = WSD.Cells(1048576, 1).End(xlUp).Row

Set PRange = WSD.Cells(2, 1).Resize(FinalRow - 1, 19)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)

Set pt = PTCache.CreatePivotTable
(tabledestination:=ResultsOutputPivot.Range("A1"), _
Tablename:="OutputPT")

pt.AddFields RowFields:="Year", ColumnFields:="Name",
PageFields:="Regin"

pt.AddDataField pt.PivotFields("Production"), "Sum of Production",
xlSum

End Sub

These codes work for a small dataset. However, when I use them to run
a larger dataset, at "Set PTCache = ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase, SourceData:=PRange)", there is message:
Run-time error '13':
Type mismatch

Any suggestion is appreciated

Thank you in advance

Fei
 
M

MacGuy

Fei,

May be a little late but this usually occurs when you have a string in your
data that has a string length greater than 255. Reduce the data strings to
255 or less and that should take care of the problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top