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!

Register Log in
  • Congratulations Mike Lewis 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

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
 
Sort by date Sort by votes
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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Oh I just realized what you suggested. I do not think that is going to work.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
615
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
628
morechocolate
morechocolate
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
221
strongm
strongm
feipezi
  • Locked
  • Question
Hoe to fix run time error 2147217900 (80040e14) syntax error in Order By clause 1
Replies
6
Views
975
Andrzejek
Andrzejek
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
288
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top