G
GDCross
I am setting up pivot tables in my worksheet and using the following code I
get the Run-time error '424' Object required. When I click on Debug, it
highlights the "Set WSD..." line. Thanks in advance.
Sub PivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivot Table")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
'Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(65536, 1).End(lxUp).Row
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("M5"), _
TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row and column fields.
PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Postion = 1
End With
'Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
get the Run-time error '424' Object required. When I click on Debug, it
highlights the "Set WSD..." line. Thanks in advance.
Sub PivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Pivot Table")
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
'Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(65536, 1).End(lxUp).Row
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("M5"), _
TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row and column fields.
PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"
' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Postion = 1
End With
'Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub