Please help with my Pivot Table VBA Code.

I followed a step by step VBA code in creating a pivot table and modified it based on my file. But I'm getting error message when I run it.

[VBA]Option Explicit
Sub Pivot()
' pivot Macro
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Sheet1")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot Table")
Dim PTCache As PivotCache
Dim PRange As Range
' 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)
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( _
"Market", "Date", "Category", "Business")
' Set up the data fields
With pt.PivotFields("Pieces Ordered")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With pt.PivotFields("Actual Sales")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
End With
With pt.PivotFields("Lost Sales")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
[/VBA]

I'm getting runtime error 1004 and it highlights this line
[VBA]Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="Pivot")[/VBA]

Attached is my file for an easier picture.

Thanks!

I'm only guessing here, but have you considered changing the name of the table to something other than "Pivot" to see if it works? I'm imagining that "Pivot" might be a reserved word in Excel.

I changed it to "PFR" but it still shows the same error:(

[vba]

Sub Pivot()

' pivot Macro

Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot Table")
Dim PTCache As PivotCache
Dim PRange As Range

' 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(2, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(2, 1).Resize(finalRow - 1, finalCol)
Set pt = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, SourceData:=PRange). _
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( _
"Country", "Date", "Category", "Business")
' Set up the data fields
With pt.PivotFields("Pieces Ordered")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With pt.PivotFields("Actual Sales")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
End With
With pt.PivotFields("Lost Sales")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
End With
' Now calc the pivot table
pt.ManualUpdate = False

End Sub
[/vba]

Duplicate thread deleted

Thank you mdmckillop.

Thank you XLD, it works perfect, but I made a little changes, instead of xlDataField I used xlColumnField. But it gave me another error with the line:
.Function = xlSum

This is my modified code
[VBA]' Set up the data fields
With pt.PivotFields("Pieces Ordered")
.Orientation = xlColumnField
.Function = xlSum
.Position = 1
End With
With pt.PivotFields("Actual Sales")
.Orientation = xlColumnField
.Function = xlSum
.Position = 2
End With
With pt.PivotFields("Lost Sales")
.Orientation = xlColumnField
.Function = xlSum
.Position = 3
End With[/VBA]

Thanks again!

Surely, if you put those fields into a column you cannot have a sum function (or position), that applies to data fields.