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

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
374
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jjasmith4

Board Regular
Joined
Aug 22, 2018
Messages
58
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

Active Member
Joined
Dec 26, 2016
Messages
374
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

Active Member
Joined
Dec 26, 2016
Messages
374
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,586
Members
430,557
Latest member
MK15

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
Top