Please help with my Pivot Table VBA Code.

[gengcas]

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!

[Aussiebear]

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.

[gengcas]

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

[Bob Phillips]

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

[mdmackillop]

Duplicate thread deleted

[gengcas]

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!

[Bob Phillips]

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