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
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