PDA

View Full Version : Please help with my Pivot Table VBA Code.



gengcas
05-31-2010, 09:12 PM
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.

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


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

Attached is my file for an easier picture.

Thanks!

Aussiebear
05-31-2010, 09:50 PM
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
05-31-2010, 11:35 PM
I changed it to "PFR" but it still shows the same error:(

Bob Phillips
06-01-2010, 01:29 AM
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

mdmackillop
06-01-2010, 10:14 AM
Duplicate thread deleted

gengcas
06-01-2010, 04:26 PM
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
' 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

Thanks again!

Bob Phillips
06-02-2010, 12:39 AM
Surely, if you put those fields into a column you cannot have a sum function (or position), that applies to data fields.