Attempting to create a PT with 3 rowfields and 2 datafields. PT is created, but nothing else works. The more changes I made the worse it gets. At the point where I've lost the basic logic of a PT.
Sub MakePTReport()
' This PT summarizes personnel budget estimates by Program,
' Subprogram and account; the number of FTE Positions and
' their associated costs.
Dim PT As PivotTable
Dim PF As PivotField
Dim CacheOfPT As PivotCache
Dim ptLastRow As Long
'Screen Updating
Application.ScreenUpdating = False
'If pivot table exists delete it
On Error Resume Next
Sheets("PTreport").Select
ActiveSheet.PivotTables("CostPT").TableRange2.Clear
'Set the cache of PT
Sheets("Summary of Data").Select
Range("A1").Select
ptLastRow = ActiveSheet.UsedRange.Rows.Count
Set CacheOfPT = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1:H" & ptLastRow))
'Create the Pivot Table
Sheets("PTreport").Select
Set PT = ActiveSheet.PivotTables.Add(CacheOfPT, Range("A3"), "CostPT")
'Put in fields
With PF
.PivotFields ("Program")
.Orientation = xlRowField
.Position = 1
End With
With PF
.PivotFields ("Sub-Program")
.Orientation = xlRowField
.Position = 2
End With
With PF
.PivotFields ("Line Seq Descrpition")
.Orientation = xlRowField
.Position = 3
End With
With PF
.PivotFields ("FTE")
.Orientation = xlDataField
.NumberFormat = "##0.00"
End With
With PF
.AddDataField ActiveSheet.PivotTables( _
"CostPT")
' .PivotFields ("FTE"), "Total FTE", xlSum
End With
With PF
.AddDataField ActiveSheet.PivotTables( _
"CostPT").PivotFields("Cost"), "Total Cost", xlSum
End With
'Display PT in Classic View
With PT
.RowAxisLayout xlTabularRow
End With
'Screen Updating
Application.ScreenUpdating = True
End Sub
Bookmarks