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

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
194
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jjasmith4

New Member
Joined
Aug 22, 2018
Messages
48
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
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,950
Members
409,848
Latest member
Blomsten
Top