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

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
439
Office Version
  1. 2019
Platform
  1. Windows
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.

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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Code:
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
 
Upvote 0
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!
 
Upvote 0
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.
Code:
                    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
                    "PivotTable1").PivotFields(ShtName), [B]ShtName[/B], xlSum


The following is the remaining pertinent part of the code.

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
 
Upvote 0

Forum statistics

Threads
1,224,041
Messages
6,176,027
Members
452,697
Latest member
CuriousSpreadsheet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top