makeitwork09
Technical User
I am using Excel 2007
The code in bold errors with the message "Run-time error '1004 Unable to get the PivotFields property fo the PivotTable Class".
If I remove the filter code complete, the pivot table is created. What is causing the filter code to error? I have tried all types of things with no success.
Thanks
The code in bold errors with the message "Run-time error '1004 Unable to get the PivotFields property fo the PivotTable Class".
If I remove the filter code complete, the pivot table is created. What is causing the filter code to error? I have tried all types of things with no success.
Code:
Sub CreateRBCTopLoans()
Dim TLCache As PivotCache
Dim TL As PivotTable
Dim TLFinalRow As Long
Dim TLLastRow As Long
Dim TLCopyLRow As Long
Dim TLpf As PivotField
Dim TLWs As Worksheet
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
wbOpenRBC.Activate
wbOpenRBC.Sheets("RBCPivotSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
TLLastRow = wbOpenRBC.Sheets("Sch B Loan Detail").Cells(Cells.Rows.Count, "A").End(xlUp).Offset(0).Row
'Create a Pivot Cache
Set TLCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=wbOpenRBC.Sheets("Sch B Loan Detail").Range("A11:BC" & TLLastRow))
'Add new worksheet
' Set wks = Worksheets.Add After:=Worksheets(Worksheets.Count)
Set TLWs = wbOpenRBC.Sheets.Add(after:=Worksheets(Worksheets.Count))
TLWs.Name = "RBCPivotSheet"
'Create the pivot table from the cache
Set TL = TLCache.CreatePivotTable( _
TableDestination:=wbOpenRBC.Sheets("RBCPivotSheet").Range("A3"), _
TableName:="RBCPivot")
'turn off updating while building the table
TL.ManualUpdate = True
With TL
'construct layout
.ShowDrillIndicators = False
.RowAxisLayout xlTabularRow
.TableStyle2 = ""
.NullString = "0"
.AddFields RowFields:=Array("loan_xref", "Name / ID", "CM Classification2")
With .PivotFields("Carrying Value")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Statement Value"
.NumberFormat = "$#,##0_);($#,##0)"
.Position = 1
End With
With .PivotFields("loan_xref")
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("loan_xref")
.ClearValueFilters
[b] [COLOR=#EF2929].PivotFilters.Add Type:=xlTopCount, _
DataField:=TL.PivotFields("Sum of Carrying Value"), Value1:=10[/color][/b]
End With
'calculate the pivot table
TL.ManualUpdate = False
TL.ManualUpdate = True
End With
End Sub
Thanks