VBA to Select Multiple Items in Pivot Table

[Bench]

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

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

"[Postal District].[Postal District].&[AB12].&[AB13]"

and

"[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

 

[Jerry Sullivan]

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

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.

 

[Bench]

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....

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.

 

[Bench]

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,

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

on the 9th will be

vDaysGone = Split("1,2,3,4,5,6,7,8", ",")

on the 10th will be

vDaysGone = Split("1,2,3,4,5,6,7,8,9", ",")

etc etc

I've tried

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

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

 

[Bench]

Managed to sort with the following, whereby vDaysGone is now = Worksheets("CUBE").Range("A70").Value

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