B
Brian Easton
Can someone take a look at this code and tell me why it's
not working. I copied it from a book (probably not a
good first step) and I can't figuer out what the hang up
is.
Sub CreatePivot()
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(xlUp).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("j2"),
TAbleName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & Colum fields
PT.AddFields RowFields:=Array("Month", "ItemNumber")
With PT.PivotFields("QuantityOnPurchaseOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
With PT.PivotFields("QuantityOnSalesOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
With PT.PivotFields("QuantityInvoice")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
End With
' Calc the Pivot Table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
not working. I copied it from a book (probably not a
good first step) and I can't figuer out what the hang up
is.
Sub CreatePivot()
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(xlUp).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("j2"),
TAbleName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & Colum fields
PT.AddFields RowFields:=Array("Month", "ItemNumber")
With PT.PivotFields("QuantityOnPurchaseOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
With PT.PivotFields("QuantityOnSalesOrder")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
With PT.PivotFields("QuantityInvoice")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
End With
' Calc the Pivot Table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub