VBA Pivot Table Fields

[helpexcel]

I have code that creates a pivot table and it works fine. However I want to make it dynamic, by using the value in B1. ANy ideas on how to update this portion of the code?

With pvt    
.PivotFields("Color").Orientation = xlPageField
        With pvt.PivotFields("Color")
            On Error Resume Next
            .PivotItems = .Range("B1")
            On Error GoTo 0
        End With
End With

 

[pbornemeier]

Your .Range("B1") is applied to the With statement: With pvt.PivotFields("Color"), so the effective statement:
pvt.PivotFields("Color").Range("B1")
is causing an error.

Also you when filter a pivot field:
1) At least one PivotItem must always be visible
2) You have to explicitly make .Visible = False for the ones you do not want to see.

Option Explicit

Sub ShowOnePivotItem()
    Dim lPIIndex As Long
    Dim pvt As PivotTable
    Dim sItem As String
    
    sItem = Worksheets("Sheet1").Range("S3").Value
    
    Set pvt = ActiveSheet.PivotTables("PivotTable1")
    
    'Clear Filters
    pvt.PivotFields("Color").ClearAllFilters
    pvt.PivotFields("Color").EnableMultiplePageItems = True
    
    With pvt.PivotFields("Color")
        'Ensure the desired PivotItem exists by trying to make it visible
        On Error Resume Next
        .PivotItems(sItem).Visible = True
        If Err.Number <> 0 Then
            MsgBox "PivotItem " & sItem & " for PivotField Color does not exist."
            End
        End If
        'Hide the others
        For lPIIndex = 1 To pvt.PivotFields("Color").PivotItems.Count
            Select Case pvt.PivotFields("Color").PivotItems(lPIIndex)
            Case sItem
                .PivotItems(lPIIndex).Visible = True
            Case Else
                .PivotItems(lPIIndex).Visible = False
            End Select
        Next
        
    End With
    
End Sub