VBA to Select Multiple Items in Pivot Table

Bench

Board Regular
Joined
Aug 27, 2009
Messages
134
Hi,

I'm trying to write a macro to select the multiple sets of the same data for several PIVOT tables. I've tried Slicers but it seems that this takes up too much processing power and always times out.

My workaround is to do a macro that picks out the said data, however when i do the below, plus another 4-500 lines i get told that there are too many line continuations

Code:
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
        "[Postal District].[Postal District].[Postal District]").VisibleItemsList = _
        Array("[Postal District].[Postal District].&[AB11]", _
        "[Postal District].[Postal District].&[AB12]", _
        "[Postal District].[Postal District].&[M2]", _
        "[Postal District].[Postal District].&[M3]", _
        "[Postal District].[Postal District].&[M4]", _
        "[Postal District].[Postal District].&[M5]", _
        "[Postal District].[Postal District].&[M6]", _
        "[Postal District].[Postal District].&[M7]", _
        "[Postal District].[Postal District].&[M8]", _
        "[Postal District].[Postal District].&[M9]", _
        "[Postal District].[Postal District].&[N1]", _
        "[Postal District].[Postal District].&[N2]", _
        "[Postal District].[Postal District].&[N3]", _
        "[Postal District].[Postal District].&[N4]") _
        "[Postal District].[Postal District].&[AB13]")
    Range("A14").Select

What i'm looking to do is express all the postcodes in one line or at least multiple post codes in one go, this is what i've tried

Code:
"[Postal District].[Postal District].&[AB12].&[AB13]"
and
Code:
"[Postal District].[Postal District].&[AB12,AB13]"

But to no avail.

Any ideas, i'm sure its just someting little but need pointing in the right direction.

Thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Here's another approach to build the Array....

Code:
Sub Make_VisibleItems_Array()
    Dim vVisibleList() As Variant, vPCodes As Variant
    Dim i As Long    

    vPCodes = Split("AB11,AB12,M2,M3,M4,M5,M6,M7,M8,M9,N1,N2,N3,N4,AB13", ",")     

    ReDim vVisibleList(LBound(vPCodes) To UBound(vPCodes))
    For i = LBound(vPCodes) To UBound(vPCodes)
        vVisibleList(i) = "[Postal District].[Postal District].&[" & vPCodes(i) & "]"
    Next i

    ActiveSheet.PivotTables("PivotTable6").PivotFields( _
        "[Postal District].[Postal District].[Postal District]") _
            .VisibleItemsList = vVisibleList
End Sub

Do you know that all the PostalCodes that you will list actually exist as PivotItems?

With OLAP data sources, an error will occur if one or more items in your .VisibleItemsList are not found.
 
Last edited:
Upvote 0
Brilliant, i'll give this a try. All the PostalCodes exist so hopefully it should work fine, this should help out massively with some projects we're doing right now.

Thanks

Here's another approach to build the Array....

Code:
Sub Make_VisibleItems_Array()
    Dim vVisibleList() As Variant, vPCodes As Variant
    Dim i As Long    

    vPCodes = Split("AB11,AB12,M2,M3,M4,M5,M6,M7,M8,M9,N1,N2,N3,N4,AB13", ",")     

    ReDim vVisibleList(LBound(vPCodes) To UBound(vPCodes))
    For i = LBound(vPCodes) To UBound(vPCodes)
        vVisibleList(i) = "[Postal District].[Postal District].&[" & vPCodes(i) & "]"
    Next i

    ActiveSheet.PivotTables("PivotTable6").PivotFields( _
        "[Postal District].[Postal District].[Postal District]") _
            .VisibleItemsList = vVisibleList
End Sub

Do you know that all the PostalCodes that you will list actually exist as PivotItems?

With OLAP data sources, an error will occur if one or more items in your .VisibleItemsList are not found.
 
Upvote 0
Hi, i'm revisiting this and have managed to make the following work for a slightly different query.

What i need to do now is make the vDaysGone a variable based upon Worksheets("Variables").Range("A1").Value

So,
Code:
vDaysGone = Split("1,2,3,4,5,6,7", ",")

on the 9th will be
Code:
vDaysGone = Split("1,2,3,4,5,6,7,8", ",")

on the 10th will be
Code:
vDaysGone = Split("1,2,3,4,5,6,7,8,9", ",")
etc etc

I've tried
Code:
vDaysGone = Worksheets("Variables").Range("A1").Value
and a few other variations with no luck.

("Variables").Range("A1") will be a TEXT formula that will change daily based upon days gone.

Any ideas what i need to do to make the VBA side of it work?

Thanks
Code:
Sub Make_VisibleItems_Array()
    Dim vVisibleList() As Variant, vDaysGone As Variant
    Dim i As Long
    vDaysGone = Split("1,2,3,4,5,6,7", ",")
    ReDim vVisibleList(LBound(vDaysGone) To UBound(vDaysGone))
    For i = LBound(vDaysGone) To UBound(vDaysGone)
        vVisibleList(i) = "[Time].[Day].&[" & vDaysGone(i) & "]"
    Next i
    ActiveSheet.PivotTables("PivotTable6").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
            
End Sub
 
Upvote 0
Managed to sort with the following, whereby vDaysGone is now = Worksheets("CUBE").Range("A70").Value

Code:
Sub Make_VisibleItems_Array()
    Dim vVisibleList() As Variant, vDaysGone As Variant
    Dim i As Long
    
    vDaysGone = Split(Worksheets("CUBE").Range("A70").Value, ",")
    
    ReDim vVisibleList(LBound(vDaysGone) To UBound(vDaysGone))
    For i = LBound(vDaysGone) To UBound(vDaysGone)
        vVisibleList(i) = "[Time].[Day].&[" & vDaysGone(i) & "]"
    Next i
    Worksheets("CUBE").PivotTables("PivotTable6").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
            
    Worksheets("CUBE").PivotTables("PivotTable1").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
            
    Worksheets("CUBE").PivotTables("PivotTable2").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
    
    Worksheets("CUBE").PivotTables("PivotTable3").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
            
    Worksheets("CUBE").PivotTables("PivotTable8").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
            
    Worksheets("CUBE").PivotTables("PivotTable7").PivotFields( _
        "[Time].[Day].[Day]") _
            .VisibleItemsList = vVisibleList
End Sub
 
Upvote 0

Forum statistics

Threads
1,211,805
Messages
6,104,079
Members
447,893
Latest member
Hoosier605

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
Back
Top