Record Macro to Create Pivot Table

[Guest]

Hi!

I used Macro Recorder to create a Pivot Table. After recording was done, I
re-ran the Macro. Then, error message came up. The message says "Run-Time
Error '1004' AddFields Method of PivotTable Class Failed"
Please refer to the following Macro Codes:

Sub test()
'
' test Macro
' Macro recorded 04/25/2007 by METALCRAFTERS
'
' Keyboard Shortcut: Ctrl+t
'
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!C1:C5").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="JobNumber", _
ColumnFields:="CostType"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TOTAL COST")
.Orientation = xlDataField
.Caption = "Sum of TOTAL COST"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Range("D2").Select
End Sub


Highlighted Sentence:
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="JobNumber", _
ColumnFields:="CostType"

Thank you very much for your help!

Ken

 

[Guest]

I found the error. The error came from wrong selection of source data.
Somehow, it was recorded as SourceData:= _"Data!C1:C5" insetad of
SourceData:= _"Data!A1:E65536"

I still don't understand why Recorder only select 5 cells