Run-time error '-2147024809(80070057)' - The PivotTable field name is not valid

Abhimannu

New Member
Joined
Apr 22, 2019
Messages
7
Trying to updated pivot table filters of different sheets of different workbook, but the getting error message "Run-time error '-2147024809(80070057)' - The PivotTable field name is not valid... "
Need help to resolve the issue and get the code running.

Code:
Private Function UPivot(PName As String, FilePath As String, colcount As Long, rangecount As Long) As String
' pivot table assignment filter


    ActiveSheet.PivotTables(PName).ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        FilePath & "!R1C1:R" & rangecount + 1 & "C" & colcount _
        , Version:=xlPivotTableVersion15)
        
    With ActiveSheet.PivotTables(PName).PivotFields("Has breached")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveWorkbook.RefreshAll
    ActiveSheet.PivotTables(PName).PivotFields("Assignment group"). _
        CurrentPage = "(All)"
        
    'START -  To unselect all Pivot Items except Blank
    Set pf = Worksheets("SUMMARY").PivotTables(PName).PivotFields("Assignment group")
    pf.ClearAllFilters


    For i = 1 To pf.PivotItems.Count
        If pf.PivotItems(i) <> "(blank)" Then
            pf.PivotItems(i).Visible = False
        End If
    Next
    'END OF - To unselect all Pivot Items except Blank

    With ActiveSheet.PivotTables(PName).PivotFields("Parent2")
        .PivotItems("External User Region").Visible = False
    End With


End Function
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It means there is an issue with the headings in whatever source range you are specifying - for example, if there are blank cells in the header row, you'd get that error.
 

Abhimannu

New Member
Joined
Apr 22, 2019
Messages
7
Thanks for your help Rory.

But I think the issue is not with the blank cell, because the Pivot code was initially used for few of the sheets with complete code (along with other activity codes) multiple times. Then I decided to make a function for pivot filter as every time i need to filter out same items. Below is the initial pivot code.

NOTE: {rangecount indicate Rows used in pivot data-table, colcount indicates columns used in pivot data-table }


ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Users\UpadhAbh\Desktop\Macro_SLM_Report_Abhimannu\[" & Ffile & "]After Removing Duplicates!R1C1:R" & rangecount + 1 & "C" & colcount _
, Version:=xlPivotTableVersion15)

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Has breached")
.PivotItems("(blank)").Visible = False
End With
ActiveWorkbook.RefreshAll
ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignment group"). _
CurrentPage = "(All)"
'START - To unselect all Pivot Items except Blank
Set pf = Worksheets("SUMMARY").PivotTables("PivotTable1").PivotFields("Assignment group")
pf.ClearAllFilters


For i = 1 To pf.PivotItems.Count
If pf.PivotItems(i) <> "(blank)" Then
pf.PivotItems(i).Visible = False
End If
Next
'END OF - To unselect all Pivot Items except Blank
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
On Error Resume Next

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignment group")
.PivotItems("DataCenter Vendors").Visible = True
.PivotItems("EME EUC Scotland IPAD").Visible = True
.PivotItems("EMEA EUC Germany Dispatch").Visible = True
.PivotItems("EMEA EUC Greece Dispatch").Visible = True
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top