Macro to filter a pivot table based on a value from another sheet

[sdoppke]

Hi All, ive scoured the forum for this and have learned a lot but am hung up. I need to filter a pivot table based on a value from another sheet. I have this but its not working

ActiveSheet.PivotTables("PivotTable3").PivotFields("Distric").CurrentPage = Sheets("MyStoreInfo").Range("B8")

Can anyone help?

sd

 

[Fazza]

Hi

Maybe specifying the format on the cell might be all that is required. Can you check what the code the macro recorder generates for the same action & emulate it?

Without VBA if the other cell has a header immediately above it (maybe easier to give the source data defined names, not dynamic; so name for header row & data) the tables can be joined in SQL. So, create a pivot table in a separate file using the external data source at the first step of the pivot table wizard, with the main data table as the source data. Follow the wizard and choose the option to edit in MS Query. Then add the table with the cell you want to filter by - either using the toolbar icon to add a table or straight in the SQL. So the SQL might be like

SELECT A.*
FROM yourdatatable A, filtercelltable B
WHERE A.Distric = B.Distric

The completed pivot table can be moved (from the new file it was created in) into the source data file. Now when your filter cell changes, all that is needed is a pivot table refresh. Obviously manually is easy, though also you could have a worksheet change event to do that. Like, sheetwhatever.pivottables(1).pivotcache.refresh

HTH. regards

 

[sdoppke]

I was able to get the macro stop erroring out, but it does something completely different?

Here is the code im using:

Sub Apply_District_Filter()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterDistrict As String
    
    
Set pvtTable = Worksheets("Variance Report").PivotTables("PivotTable3")
Set pvtField = pvtTable.PivotFields("District")
filterCost = Worksheets("MyStoreInfo").Range("B8")
    For Each pvtItem In pvtField.PivotItems
    If pvtItem.Value = filterDistrict Then
        pvtItem.Visible = True
    Else
        pvtItem.Visible = False

        Exit For
    End If
Next pvtItem
End Sub

it filters out one district out of hundreds? Its supposed to filer all out except the value (District) of B8 on MyStoreInfo.

Anyone see where I am going wrong?

Thanks all in advacne

sd

 

[sdoppke]

Here is what im using now and now it doesnt work at all sais pvtItem=Nothing

Set pvtTable = Worksheets("Variance Report").PivotTables("PivotTable3")
Set pvtField = pvtTable.PivotFields("District")
    
filterCost = Worksheets("MyStoreInfo").Range("I2")
    
For Each pvtItem In pvtFields.PivotItem
    If pvtItem.Value = filterCost Then
        pvtField.CurrentPage = filterCost
        Exit For
    End If
Next pvtItem

Anyone have any ideas?

sd

 

[Fazza]

Can you post some sample data? It greatly aids in understanding the requirement & can be crucial to the solution.

It seems like you're not interested in my first solution? Maybe it doesn't do what you want. In fact, exactly what you want isn't crystal clear to me. If the other sheet contains, for example, "apple" - I don't know if you want to only see the "apple" records in the results or all records other than "apple".

Here is another non-code solution. Add a column to the source data. Populate with a formula to return TRUE or FALSE and use that field as a page field in the pivot table. Again, I'm not clear what you want. Formula might use MATCH. So, =isnumber(match(code,table,0))

It is worth putting more time into defining the question 'cause you'll get a better response.

regards

 

[Fazza]

PS
Which version of Excel?
regards

 

[sdoppke]

PS
Which version of Excel?
regards

Excel 2007