Filter Pivot Table with Contents of Cell

[diedrich08]

Hi All,

I am attempting to filter a "temporary" pivot table on a loop, by the contents of a cell. The pivot table is generated on a worksheet which is removed following pivot table generation, and output to another location. I have attempted to filter this pivot table based on the contents of a cell specified by my loop but have been unsuccessful.The majority of information indicates that a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" sub must be specified for the worksheet that the pivot table is located in, but since I am inserting my pivot table into a temporary worksheet, I cannot do this.

I'm sure there is a way to reference a cell for a filter and hopeful someone on Eileen's Lounge can help me with my issue!

Below is the part of my relevant code. I have included one pivot table filter and am hopeful I can just insert something in there. If you need more code, please let me know.

Thanks everyone!

Diedrich

Code: Select all

        'Insert temporary worksheet for pivot table
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "Temp_Pivot"
            
        'Define worksheet
        Dim Temp_Pivot As Worksheet
        Set Temp_Pivot = Worksheets("Temp_Pivot")
        
        'Insert pivot table
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim rng1 As Range
            Set wb = ActiveWorkbook
            Set ws = wb.Sheets("Data")
            Set rng1 = ws.Cells.Find("*", ws.[A1], xlFormulas, , , xlPrevious)
            
            
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                "Data!R1C1:R" & CStr(rng1.Row) & "C" & CStr(rng1.Column), Version:=xlPivotTableVersion14).CreatePivotTable _
                TableDestination:=Temp_Pivot.Range("A1"), TableName:="PivotTable31", _
                DefaultVersion:=xlPivotTableVersion14
            Sheets("Temp_Pivot").Select
            Cells(2, 1).Select
            
            'Filter for criteria 1
            With ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1")
                .Orientation = xlPageField
                .Position = 1
            End With
            ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1"). _
                ClearAllFilters
            ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1").CurrentPage = _
                "Yes"

[Rudi]

See if this provides some assistance...

Code: Select all

        'Insert temporary worksheet for pivot table
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "Temp_Pivot"
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "Temp_Filter"
            
        'Define worksheet
        Dim Temp_Pivot As Worksheet
        Dim Temp_Filter As Worksheet
        Set Temp_Pivot = Worksheets("Temp_Pivot")
        Set Temp_Filter = Worksheets("Temp_Filter")
        
        'Insert pivot table
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim rng1 As Range
            Dim rng2 As Range
            Dim i As Integer
            Set wb = ActiveWorkbook
            Set ws = wb.Sheets("Data")
            Set rng1 = ws.Cells.Find("*", ws.[A1], xlFormulas, , , xlPrevious)
            Set rng2 = ws.Cells.Find("Criteria_1", ws.[A1], xlFormulas, , , xlNext)
            
            Intersect(rng1.CurrentRegion, rng2.EntireColumn).AdvancedFilter _
                xlFilterCopy, rng2, Temp_Filter.Cells(1), True
            
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                "Data!R1C1:R" & CStr(rng1.Row) & "C" & CStr(rng1.Column), Version:=xlPivotTableVersion14).CreatePivotTable _
                TableDestination:=Temp_Pivot.Range("A1"), TableName:="PivotTable31", _
                DefaultVersion:=xlPivotTableVersion14
            Sheets("Temp_Pivot").Select
            Cells(2, 1).Select
            
            'Filter for criteria 1
            With ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1")
                .Orientation = xlPageField
                .Position = 1
            End With
            ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1").ClearAllFilters
            With Temp_Filter.Cells(1).CurrentRegion
                For i = 2 To .Rows.Count
                    ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1").CurrentPage = .Cells(i).Value
                    'Your code here to process the filtered pivot further
                Next i
            End With
            Application.DisplayAlerts = False
            Temp_Filter.Delete
            Application.DisplayAlerts = True

[diedrich08]

Thanks for the help Rudi,

When I use the code, I get "Run-time error '1004': Application-defined or object-defined error. Do you know what might be causing this? This error has occurred for every way I attempt to alter the code. The error occurs on the line for "Intersect(rng1.CurrentRegion, rng2.EntireColumn).AdvancedFilter _xlFilterCopy, rng2, Temp_Filter.Cells(1), True" and I have tried to remove this line and the "Temp_Filter" reference by using a cell defined in my loop as the filter cell; however, I get the same error on the line which has ".CurrentPage"

For clarity, I already have the cell defined earlier in the code (on a loop) that I want to reference, and I am wondering if "Temp_Pivot" sheet is necessary? The cell I would like to reference is "Table_2.Range("$B" & a)", which works fine throughout the rest of the code. Am I able to use "Table_2.Range("$B" & a)" rather than finding an intersect on an additional worksheet (i.e. "Temp_Filter")?

Thanks again for your help, it's greatly appreciated.

Diedrich

[Rudi]

Hi,

I'm on my phone currently so I can't help in detail, but try this:

Scrap the Temp_Filter sheet as we won't need this if Table_2.Range("$B" & a) is returning the appropriate filter items for the pivot.
The .CurrentPage is returning and error (I assume) as you need to add .Value behind Table_2.Range("$B" & a).
Regarding if Temp_Pivot is not necessary; I don't have enough detail to determine that. If you already have a pivot, I guess you can use that and filter it, then send the copy to a destination.

Here is my version of the revised code (as far as I understand your needs)

Code: Select all

        'Insert temporary worksheet for pivot table
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "Temp_Pivot"
            
        'Define worksheet
        Dim Temp_Pivot As Worksheet
        Dim Temp_Filter As Worksheet
        Set Temp_Pivot = Worksheets("Temp_Pivot")
        
        'Insert pivot table
            Dim wb As Workbook
            Dim ws As Worksheet
            Dim rng1 As Range
            Dim i As Integer
            Set wb = ActiveWorkbook
            Set ws = wb.Sheets("Data")
            Set rng1 = ws.Cells.Find("*", ws.[A1], xlFormulas, , , xlPrevious)
            
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                "Data!R1C1:R" & CStr(rng1.Row) & "C" & CStr(rng1.Column), Version:=xlPivotTableVersion14).CreatePivotTable _
                TableDestination:=Temp_Pivot.Range("A1"), TableName:="PivotTable31", _
                DefaultVersion:=xlPivotTableVersion14
            'Sheets("Temp_Pivot").Select
            'Cells(2, 1).Select
            
            'Filter for criteria 1
            With ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1")
                .Orientation = xlPageField
                .Position = 1
            End With
            ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1").ClearAllFilters
            ActiveSheet.PivotTables("PivotTable31").PivotFields("Criteria_1").CurrentPage = _
                Table_2.Range("$B" & a).Value ' or ws.Range("B" & a).Value

[Rudi]

Hi,

In case you are subscribed to this post, note that my previous reply was from a cell phone. I was travelling at the time I tried to help.
I'm back on PC again, so I have edited my reply above with more detail.

Cheers

[diedrich08]

Works great Rudi! This is so much simpler than many of the blog post I have read.

Thanks again for all your help and contributing to this message board!

Diedrich