VBA Pivot Table - Check if Field Exists and Check or Uncheck

[OilEconomist]

Thanks in advance for any suggestions and I will provide feedback.

How do I modify the following code to check if those Pivot Table fields exist and de-select or select them. Here is a sample code.

Sub PivotItem()
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("PCN")
    
        .AutoSort xlManual, "PCN"
        .PivotItems("ChoiceA").Visible = False
        .PivotItems("ChoiceB").Visible = False
    
    End With


End Sub

Once again thanks!

 

[jjasmith4]

Sub PivotItem()
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("PCN")
    
        .AutoSort xlManual, "PCN"

        On Error Resume Next
        .PivotItems("ChoiceA").Visible = False
        .PivotItems("ChoiceB").Visible = False
        On Error Goto 0
    
    End With

End Sub

 

[OilEconomist]

Thank you and apologies for the late response.

I have not tried this yet as I am in the middle of altering my code and it won't run right now. I should have it done this weekend where I can test this out.

Thank you so much and I will let you know once I've tested!

 

[OilEconomist]

So JJASmith4 I tried what you had and it didn't work, so I tried the following and was getting an error. Any idea why or any suggestions? Thanks in advance and apologies for the late response.

What I'm actually doing is looping through a column, and if a cell meets a criteria, I make a sheet based on the cell's value and set that value to the string "ShtName".

But my code is giving me an error on this line, where I think "ShtName" is the issue.

                    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
                    "PivotTable1").PivotFields(ShtName), [B]ShtName[/B], xlSum

The following is the remaining pertinent part of the code.

'The Pivot Table always starts out with "Test" selected
           ShtNameOld = "Test"

 For i = 2 To LastRowK
            'Activates the "Control" tab
             Worksheets("Control").Activate
                
            If Cells(i, 11).Value = "Yes" And Cells(i, 12).Value = "Yes" Then
            
                'Create the Tab
                    ShtName = Cells(i, 13).Value
                    Sheets("Template.Line.Item.Data").Copy Before:=Sheets("End")
                        ActiveSheet.Name = ShtName
                        
                'Selects the Pivot Table
                    Sheets("Pivot.Table.Data").Select
                    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
                    "PivotTable1").PivotFields(ShtName), ShtName, xlSum
                    ActiveSheet.PivotTables("PivotTable1").PivotFields(ShtNameOld). _
                    Orientation = xlHidden
                    
                    'Selects the data of interest
                        Worksheets("Pivot.Table.Data").Range("D5:" & LastClmPTDLtr & LastRowPTD).Copy
                        Worksheets(ShtName).Range("V11").PasteSpecial Paste:=xlPasteFormulas
                    
                        Worksheets("Pivot.Table.Data").Range("B5:B" & LastRowPTD).Copy
                        Worksheets(ShtName).Range(LastClmTLIDLtr & "11").PasteSpecial Paste:=xlPasteFormulas
                        
                        ShtName = ShtNameOld
                
            ElseIf Cells(i, 11).Value = "Yes" And Cells(i, 12).Value = "No" Then
                'Creates the Tab
                    ShtName = Cells(i, 13).Value
                    Sheets("Template.Line.Item.Data").Copy Before:=Sheets("End.CF.Line.Items")
                        ActiveSheet.Name = ShtName
            
        
            End If
        
        Next i