VBA - Pivot Tables - isolating categories

[Aclare87]

Hey, I'm working on a bunch of pivots using VBA. I need to isolate "Bermuda" in a list of countries and am looking for an easier way to isolate. The list is going to get a lot longer and removing fields individually definitely can't be a solution. Also with Visible=False process, after a bunch of them, I had to end and start the with again to continue. Anyone?? This can't be right. Thanks!

With ActiveSheet.PivotTables("BermudaCanada").PivotFields("Domicile Country")
.PivotItems("Australia").Visible = False
.PivotItems("Austria").Visible = False
.PivotItems("Belgium").Visible = False
.PivotItems("Canada").Visible = False
.PivotItems("Cayman Islands").Visible = False
.PivotItems("France").Visible = False
.PivotItems("Germany").Visible = False
.PivotItems("Hong Kong").Visible = False
.PivotItems("Indonesia").Visible = False
.PivotItems("Ireland").Visible = False
.PivotItems("Italy").Visible = False
.PivotItems("Korea").Visible = False
.PivotItems("Netherlands").Visible = False
.PivotItems("Singapore").Visible = False
End With
With ActiveSheet.PivotTables("BermudaCanada").PivotFields("Domicile Country")
.PivotItems("Switzerland").Visible = False
.PivotItems("UK").Visible = False
.PivotItems("USA").Visible = False
End With

 

[Jerry Sullivan]

Hi Aclare87,

There are some code examples available that vary a bit based on some details of how you will use the code.

1. Will you be setting all PivotItems except one to be hidden, or will you be making more than one PivotItem visible at the same time?

2. What area of the PivotTable is your Domicile Country PivotField? (Report Filters, Row Labels or Column Labels)

3. What version of Excel are you using?

BTW, You shouldn't have to restart a With Block after listing several items as you describe. You might have encountered some other error, and that might have appeared to be cause, but there's no reason to have to break those up as shown in your example.

 

[Aclare87]

Hi Jerry, thanks for the response.

1. Yes I would like to hide all pivot items but one (Bermuda). I'd think i'm looking to clear all items andf then select as needed.
2. Domicile region is a column label
3. I'm using Excel 2007.

Thanks!

 

[Jerry Sullivan]

You can use this VBA Function to clear all items in the field and select the one item you want to show.

Public Function Filter_PivotField_Single_Item(ptField As PivotField, _
        sItem As String)
'---Filters the PivotField to make only sItem Visible
    Dim i As Long
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    With ptField
        .Parent.ManualUpdate = True
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(sItem).Visible = True
        For i = 1 To .PivotItems.Count
            If (.PivotItems(i) = sItem) <> .PivotItems(i).Visible Then
                .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
            End If
        Next i
    End With
    
CleanUp:
    ptField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Here is an example of how the function could be called from a macro or button.
It assumes the item you want to show is in Cell B1 of the activesheet.

Sub PivotFilterExample()
    Dim sItemToShow As String
    Dim PT As PivotTable
       
    With ActiveSheet
        sItemToShow = .Range("B1").Value
        Set PT = .PivotTables("BermudaCanada")
        Call Filter_PivotField_Single_Item( _
            ptField:=PT.PivotFields("Domicile Country"), _
            sItem:=sItemToShow)
    End With
End Sub

Another option is to use Event code to trigger the running of the macro whenever B1 is changed.

 

[Aclare87]

Hey,

Thanks again Jerry. That code is a little over my head (i'm still new to this. Honing skills in real time as I'm trying to automate much of my work). I'd thought the clearing would just be an additional line of code hah. I'll find some time to comb through what you sent this weekend to see if I can figure out what's going on there. I've never used a public function before (only done regular sub xxx() ) so far. I know there's private subs, functions, etc, but haven't implemented any yet so lacking practice. Nice this is forcing me to move forward with my studying.

I'll reply again next week with either questions or a big thanks!

Have a great weekend

 

[Jerry Sullivan]

Sorry if that code is a little overwhelming for the task.

At its core, it's as simple as you thought it should be:
"Make my 1 item Visible, then Hide all the other items."

This simpler version will accomplish the same result in most situations.

Sub Filter_PivotField_Single_Item(ptField As PivotField, _
        sItem As String)
    Dim i As Long
 
    With ptField
        .PivotItems(sItem).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> sItem Then
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
End Sub

Much of the added code in the more complex version is designed to make the process run faster and handle errors or exceptions. Perhaps you can digest this simpler version first, then try using both versions on a PivotTable with a large number of items to see if you notice the difference.

 

[Aclare87]

Thanks that really helps a lot! yes that is certainly a lot easier to digest. Haven't had a chance yet to look through the other code but looking forward to it.

Thanks again for your quick responses

 

[DaveSp]

Good morning, in searching for a solution to my issue, I came across this thread and the code, I have is darn nnear identical.

I have an invoice for a client. In that invoice there are 50+ "subclient". I've created a pivot table to allow me to view each indivudal client and their invoices detail. Each sub client has a unique id, such as "E00". I need to develope a macro to display or make visable the invoice detail for subclient "E00", then copy it to a newly created worksheet tab with the same name. I have code to do everythign I need to do and all works, except being able run a loop to bring up each subclient. I've tried a number of different tactics prior to finding this post.

I tried the simplied version in the previous reply by JS411, but get errors.

Below is the code I am using.

I'd appreciate any help provided.

Thanks All

Sub CopyData()
'
' CopyData Macro
'
Dim ptField As PivotField
Dim sItem As String
Dim i As Long
ClientNumber = Array("00E0000000", "00E0200000", "00E0300000", "00E0400000", "00E0500000")
ClientList = Array("E00", "E02", "E03", "E04", "E05")
Counter = 0
For CopyPivotData = 0 To 5
Client = ClientNumber(Counter)
ClientTab = ClientList(Counter)
With ptField
.PivotItems(sItem).Visable= True ' this is where the code bombs out - with Run-time error 91 - object variable or with block variable not set
For i = 1 To .PivotItems.Count
If (.PivotItems(i) = sItem) <> .PivotItems(i).Visible Then
.PivotItems(i).Visible = Not (.PivotItems(i).Visible)
End If
Next i
End With

'-----find the cell range to copy
FirstRowofTheData = ActiveCell.Address
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
LastRowOfTheData = ActiveCell.Address
TheRange = "(" & FirstRowofTheData & ":" & LastRowOfTheData & ")"
Range(TheRange).Select
Selection.Copy
'-----change to new correct worksheet tab
Sheets(ClientTab).Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Counter = Counter + 1
Next CopyPivotData
End Sub

 

[Jerry Sullivan]

Hi Dave, That error is occuring because ptField and sItem haven't been assigned before that line.

Have you tried using calls to the function PivotField_Single_Item instead of embedding that code in your Sub?

 

[DaveSp]

Thanks Jerry. While waiting on a reply, I was working on my own version of your code, figuring I was missing tsomethign somewhere. Whiel doing that I realized I did not initalize ptField and sItems correctly.... DAH!

As for the calls... I have not yet come to that point while teaching myself VBA coding, however, I suspect I may need to learn that functionality now.

If I stumble too much, I will reply to this thread for addtional assistance.

Thanks much for your help!!

Dave