Pivot Table change filter based on cell references

[dazfoz]

Hi all,

I have a sheet which has an external database connection (Don't know if that is relevant or not)

Within my sheet are a number of Pivot Tables, which I have to manually change the filters on them all when I want to change the data set that I am looking at. Attached is the VBA recorder for when I change the weeks to 2015 1/2/3/4/5 & 2016 1/2/3/4/5. What I would like to do, is have a separate feeder page which has a list (In this instance 2015 1, 2015 2, 2015 3 etc) and hitting a button then updated the filter with this selection. The selection will not always be constant, it could be 6 x dates, it could be 8 it could be 10, so could do with the code being able to handle dynamic range lengths.

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array( _
        "[Dim Date].[YearWeek].[Dim Date Week].&[1]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[2]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[3]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[4]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[5]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[1]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[2]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[3]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[4]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[5]&[2016]")

I know that I could link each

&[1]&[2015]

to a cell/s, but this wont allow me to change the number of filter selections in my feeder sheet.

I would also like this to take effect on every sheet within the workbook at the same time.

Thanks in advance

Daz

 

[dazfoz]

OK, now im really stuck!

I cant even manage to change the week number and year to reference a cell in a feeder sheet.

Im getting a "compile error expected list separator or )" error, ive tried everything I can but my limited VBA knowledge is really holding me back!

ActiveSheet.PivotTables("CUBE").PivotFields( _
        "[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array( _
        "[Dim Date].[YearWeek].[Dim Date Week].&Sheets("Data Fields").Range("c6").Value&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[41]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[42]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[43]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[44]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[40]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[41]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[42]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[43]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[44]&[2016]")

but if I do

Sub ReadCell()
 MsgBox Sheets("Data Fields").Range("c6").Value
 
 End Sub

the msgbox spits out the correct number.

Im sure its a very simple thing that im missing, but any help would be appreciated.

 

[dazfoz]

Morning All,

After quite a bit of tinkering, I have managed to apply the filter selection to a number of Pivots, but only the selection that is hard-coded into the VBA in the square brackets.

I still cant get the numbers to link to a cell.

My new and improved! code is as follows;

Sub Change_Multiple_Pivots()

Dim pt As PivotTable
Dim ws As Worksheet


    For Each ws In ActiveWorkbook.Worksheets

    For Each pt In ws.PivotTables

   

        Select Case pt.Name

            Case "CUBE", "CUBE 2", "CUBE 3"

                pt.PivotFields( _
        "[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array( _
        "[Dim Date].[YearWeek].[Dim Date Week].&[40]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[41]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[42]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[43]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[44]&[2015]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[40]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[41]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[42]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[43]&[2016]", _
        "[Dim Date].[YearWeek].[Dim Date Week].&[44]&[2016]")

            Case Else

        End Select

    Next pt

   Next ws


End Sub

I hoping that someone can help as i'm slowly losing the will to live!

 

[dazfoz]

Well its taken me all day, but I think I have figured a rather 'clunky' way of doing it if anybody is interested!

Sub Change_Multiple_Pivots()

Dim pt As PivotTable
Dim ws As Worksheet

Dim Week1 As String
Dim Week2 As String
Dim Week3 As String
Dim Week4 As String
Dim Week5 As String
Dim Week6 As String
Dim Week7 As String
Dim Week8 As String
Dim Week9 As String
Dim Week10 As String
Dim Year1 As String
Dim Year2 As String
    
Week1 = Sheets("Data Fields").Range("e6").Value
Week2 = Sheets("Data Fields").Range("e7").Value
Week3 = Sheets("Data Fields").Range("e8").Value
Week4 = Sheets("Data Fields").Range("e9").Value
Week5 = Sheets("Data Fields").Range("e10").Value
Week6 = Sheets("Data Fields").Range("e11").Value
Week7 = Sheets("Data Fields").Range("e12").Value
Week8 = Sheets("Data Fields").Range("e13").Value
Week9 = Sheets("Data Fields").Range("e14").Value
Week10 = Sheets("Data Fields").Range("e15").Value
Year1 = Sheets("Data Fields").Range("e18").Value
Year2 = Sheets("Data Fields").Range("e19").Value


    For Each ws In ActiveWorkbook.Worksheets

    For Each pt In ws.PivotTables

   

        Select Case pt.Name

            Case "CUBE", "CUBE 2", "CUBE 3"

                pt.PivotFields("[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array(Week1, Week2, Week3, Week4, Week5, Week6, Week7, Week8, Week9, Week10)

            Case Else

        End Select
        
        Select Case pt.Name

            Case "CUBE 5", "CUBE 8"

                pt.PivotFields("[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array(Week6, Week7, Week8, Week9, Week10)

            Case Else

        End Select
        
        Select Case pt.Name

            Case "CUBE 4", "CUBE 7"

                pt.PivotFields("[Dim Date].[YearWeek].[Dim Date Week]").VisibleItemsList = Array(Week1, Week2, Week3, Week4, Week5)

            Case Else

        End Select
        
        Select Case pt.Name

            Case "CUBE 6"

                pt.PivotFields("[Dim Date].[YearWeek].[Dim Date Year]").VisibleItemsList = Array(Year1)

            Case Else

        End Select

        Select Case pt.Name

            Case "CUBE 9"

                pt.PivotFields("[Dim Date].[YearWeek].[Dim Date Year]").VisibleItemsList = Array(Year2)

            Case Else

        End Select
    Next pt

   Next ws

Call SortChartAxis
    

End Sub

Hopefully this may help someone with a similar issue, and if you have any idea of how I could speed things up please feel free to post!

Daz