Using Variables in Pivot Table Macro

[sulley333]

I am trying to use the following type of code below but have a problem. My values are listed in a range from cells H13:P13 but am not sure how many will be populated (coming from a list box). I need those values to be the filter in a pivot table like below. Any ideas??

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
    .PivotItems(brand_one).Visible = True
    For Each pi In .PivotItems
    Select Case UCase(pi.Name)
         Case UCase(brand_one), UCase(brand_two)
            pi.Visible = True
         Case Else
            pi.Visible = False
      End Select
    Next pi
    End With

 

[pbornemeier]

This code seems to be redundant, but at least one item must be visible in the Pivot field and this accounts for that. I believe this will do what you are asking for.

Sub DisplayPivotSelectedBrands()

    Dim iX As Integer
    Dim sSelectedBrand As String
    Dim pi As PivotItem
    
    'Is at least one item in listbox selected?
    For iX = 0 To frmImportFiles.lstFilenames.ListCount - 1
        If frmImportFiles.lstFilenames.Selected(iX) Then
            sSelectedBrand = frmImportFiles.lstFilenames.List(iX)
            Exit For
        End If
    Next
    If Len(sSelectedBrand) > 0 Then
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
        
            'Set that item visible
            .PivotItems(sSelectedBrand).Visible = True
            
            'Set that item visible all others not - procedure appears to be duplicative, but
            'have to ensure at least one item is visible.
            For Each pi In .PivotItems
                Select Case UCase(pi.Name)
                Case UCase(sSelectedBrand)
                    pi.Visible = True
                Case Else
                    pi.Visible = False
              End Select
            Next pi
            
            'Turn on all that are selected
            For iX = 0 To frmImportFiles.lstFilenames.ListCount - 1
                If frmImportFiles.lstFilenames.Selected(iX) Then
                    .PivotItems(sSelectedBrand).Visible = True
                End If
            Next
        End With
    Else
        MsgBox "No brands selected"
    End If

End Sub

 

[sulley333]

Hi Phil! Thanks so much for the reply! I am going to have this for two different fields...can I do the If statement just before creating the pivot table? also, what is frmImportFiles.lstFilenames?

Thanks!

 

[sulley333]

Hi Phil! I figured out my question above but now it is only selecting the first selection in the list box. I have a multiselect list box...how does it work for that?

 

[pbornemeier]

I did a lot of cut and pasting without sufficient testing. Fix follows (still untested):

            For iX = 0 To frmImportFiles.lstFilenames.ListCount - 1
                If frmImportFiles.lstFilenames.Selected(iX) Then
                    .PivotItems(sSelectedBrand).Visible = True
                End If
            Next

replace underlined blue text above with this:

frmImportFiles.lstFilenames.List(iX)

 

[sulley333]

I have the following and doesn't seem to work:

Set ctlx = ws_dash.Shapes("ListBox_Caption").OLEFormat.Object
    Set ctly = ws_dash.Shapes("ListBox_Foundation").OLEFormat.Object
 
    'Is at least one item in listbox selected?
    For iX = 0 To ctlx.Object.ListCount - 1
        If ctlx.Object.Selected(iX) Then
            sSelectedCaption = ctlx.Object.List(iX)
            Exit For
        End If
    Next
    For iY = 0 To ctly.Object.ListCount - 1
        If ctly.Object.Selected(iY) Then
            sSelectedFoundation = ctly.Object.List(iY)
            Exit For
        End If
    Next
 
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Caption")

            
            'Set that item visible
            .PivotItems(sSelectedCaption).Visible = True
            
            'Set that item visible all others not - procedure appears to be duplicative, but
            'have to ensure at least one item is visible.
            For Each pi In .PivotItems
                Select Case UCase(pi.Name)
                Case UCase(sSelectedCaption)
                    pi.Visible = True
                Case Else
                    pi.Visible = False
              End Select
            Next pi
            
            'Turn on all that are selected
            For iX = 0 To ctlx.Object.ListCount - 1
                If ctlx.Object.Selected(iX) Then
                    .PivotItems(ctlx.Object.List).Visible = True
                End If
            Next
        End With

 

[pbornemeier]

I can't easily test the code without the data to make a correct PT. When you run the code does it error out? Or does nothing happen?

Open the VB window about half-size and arrange it so you can see the code and the pivot table. When you step through the code do the lines of code execute in the expected order?

Please provide a dozen lines of data that you make the pivot table from using Excel Jeanie (see link in my sig) so I can test the code on a valid PT

 

[sulley333]

Unfortunately I can't download things on my computer because I don't have administrative rights. When I run the code...it gets all the way to

.PivotItems(ctlx.Object.List).Visible = True

and gives me the error of Unable to get the PivotItems property of the PivotField class

 

[pbornemeier]

Change the line in post #8 to

     .PivotItems(ctlx.Object.List(ix)).Visible= True

 

[sulley333]

You ROCK Phil!!!!! Thank you so much for helping me with this!!! Have a fabulous weekend!