Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-time error '1004 Unable to get the PivotFields property fo the PivotTable Class

Status
Not open for further replies.

makeitwork09

Technical User
Sep 28, 2009
170
US
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.

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
 
hi,

On your sheet, in your PivotTable, try to set a filter on a DATA FIELD. I think that you will find that you cannot!

So what you are prevented from doing do in native Excel, you cannot do in VBA.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks for the reply Skip,

When I used the macro recorder I got the following code for the same place where I am receiving the error in my version.

Code:
ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref").PivotFilters. _
        Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Statement Value"), Value1:=10

I my version, I tried changing
Code:
DataField:=TL.PivotFields("Sum of Carrying Value")

to

Code:
DataField:=TL.PivotFields("Statement Value")

but I received an object related error.

Perhaps I am not understanding what you are referencing, because I would think I could do the same thing that the recoreded macro is doing. Of course, unless that is not what I am doing.

Thanks
 
To get an idea of what Excel would do, I started with selecting the objects to appear in the Pivot Table. Below is what is produced. In bold is the same area that is erroring for me, even after I change my code to use Statement Value instead of Sum of Carrying Value.

Code:
 With ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name / ID")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CM Classification2")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Carrying Value"), "Sum of Carrying Value", xlSum
    ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Carrying Value" _
        )
        .Caption = "Statement Value"
        .NumberFormat = "#,##0_);(#,##0)"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref").ClearAllFilters
[b]    ActiveSheet.PivotTables("PivotTable1").PivotFields("loan_xref").PivotFilters. _
        Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
        PivotFields("Statement Value"), Value1:=10[/b]

When I change my line mentioned in the original post to the code below, the error changes to Application-defined or object-defined error. I am sure the error is something silly, but it is not jumping out at me.

Code:
        With .PivotFields("loan_xref")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlTopCount, _
                DataField:=TL.PivotFields("Statement Value"), Value1:=10
        End With
 
So you're assigning a filter to loan_xref that has value(s) equal to another PivotField, Statement Value, that has a value of 10?

Code:
   With TL
        With .PivotFields("loan_xref")
            .ClearValueFilters
            .PivotFilters.Add Type:=xlTopCount, _
                DataField:=TL.PivotFields("Statement Value"), Value1:=10
        End With
   End With

Why would that not be the same as
Code:
   With TL
        With .PivotFields("loan_xref")
            .ClearValueFilters
            .PivotFilters.Add Type:=xlTopCount, _
                DataField:=10
        End With
   End With

I guess I'm haveing difficulty understanding what you are attempting to accomplish apart from the code.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Hi Skip,

The filer is doing the top 10 values.

If I were doing it manually I do the following:

Click the drop-down arrow on loan_xref

From the value filter choices I select Top 10

From left to right the choices are

Show Top 10 Items by Statement Value (because I changed the caption from Sum of Carrying Value)

There are no issues when I do it that way. In the last post that I shared, I show the resulting recorded macro.

I will give what you describe above a tryl

Thanks

 
Oh I just realized what you suggested. I do not think that is going to work.
 
I still have no clue what was wrong with that I was originally doing, but I got this to work by using the recorded version of the code. Instead of using PivotTable1 as the name of the table, I used the name that I created.

Is the the code that is working.

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

    wbOpenRBC.Worksheets("Sch B Loan Detail").Range("BC12") = 500
    
    TLLastRow = wbOpenRBC.Sheets("Sch B Loan Detail").Cells(Cells.Rows.Count, "BC").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")
    
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("loan_xref")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("Name / ID")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("CM Classification2")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("RBCPivot").AddDataField ActiveSheet.PivotTables( _
        "RBCPivot").PivotFields("Carrying Value"), "Sum of Carrying Value", xlSum
    ActiveSheet.PivotTables("RBCPivot").RowAxisLayout xlTabularRow
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("Sum of Carrying Value" _
        )
        .Caption = "Statement Value"
        .NumberFormat = "#,##0_);(#,##0)"
    End With
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("CM Classification2")
        .PivotItems("42").Visible = False
    End With
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("Name / ID")
        .PivotItems("1").Visible = False
    End With
    With ActiveSheet.PivotTables("RBCPivot").PivotFields("loan_xref")
        .PivotItems("500").Visible = False
    End With
    ActiveSheet.PivotTables("RBCPivot").PivotFields("loan_xref").AutoShow _
        xlAutomatic, xlTop, 10, "Statement Value"
    Range("B6").Select
    ActiveSheet.PivotTables("RBCPivot").PivotFields("Name / ID").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    Range("D5").Select
    ActiveSheet.PivotTables("RBCPivot").PivotFields("CM Classification2").AutoSort _
        xlDescending, "Statement Value", ActiveSheet.PivotTables("RBCPivot"). _
        PivotColumnAxis.PivotLines(1), 1
    ActiveSheet.PivotTables("RBCPivot").PivotFields("CM Classification2").AutoSort _
        xlDescending, "Statement Value", ActiveSheet.PivotTables("RBCPivot"). _
        PivotColumnAxis.PivotLines(1), 1
    ActiveSheet.PivotTables("RBCPivot").PivotFields("loan_xref").AutoSort _
        xlDescending, "Statement Value", ActiveSheet.PivotTables("RBCPivot"). _
        PivotColumnAxis.PivotLines(1), 1
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top