Is it possible to filter a Pivot Table field using a "contains" keyword from another cell? - VBA Question

Hi,

You could write some code to loop through the pivot field items - best with two loops. First to make every item visible and check if there will be any to show (to avoid the error of trying to hide every single item) and the second loop to hide the items which should be hidden. Suggest setting the pivot cache to manual update at the start of the code and reset it at the end. There will be examples in some old threads - I'll have a look today.

Another way which may not be ok for you, but would be much less work is to use a parameterised query. Such as http://www.dailydoseofexcel.com/archives/2005/10/28/show-all-with-a-range-parameter-query/ The parameterised query can be set to refresh automatically on change to the input cell - your cell showing "Green". If you really need a pivot table, give the query results a defined name and make that the source data for the pivot table. The query could be on a hidden sheet. The code then is just a single line to refresh the pivot table. wksCodeRef.pivottables(1).pivotcache.refresh

HTH, Fazza

some examples, there will be others if you search a bit. HTH
http://www.mrexcel.com/forum/showthread.php?t=346590
http://www.mrexcel.com/forum/showthread.php?t=366061

Thank you Fazza!

I will the the above suggestions a try. The reason why I wanted to have the Pivot Table reference cell "G25" was to make it easier for people who were not comfortable with Excel.

OK. You can still use cell G25 - just make it the cell that controls the parameterised query.

I thought overnight of a better approach than the query table. Have a worksheet change event edit the pivot table's SQL on changes to cell G25.

Such as, create the pivot table in a new workbook (separate to the data workbook, to avoid memory leak problems). Once created, you can move the resultant worksheet back into the source data file. When creating the pivot table, take the external data option at the first page of the wizard. At the completed pivot tables, insert the code below - modify to suit - into the worksheet's code module.

Regards, Fazza

My quick & dirty solution was a simple "find & replace" exercise using wildcards.

For example, I had shee of location data. Street names in one column, zip codes in another, location type in another.

I wanted to filter on any location that was a "school".

Since the "location type" values didn't have a uniform naming convention, I had to find all records that simply has "school" somewhere in the "location type" column.

To do this, I first made a copy of the sheet to a new tab, then on the new tab, highlighted the "location type" column, and did a "Find & Replace".

My "Find What" value was "*school* (remove quotes).

My "Replace with" value was "school"

I then sorted the records by the "location type" value, grouping all "school" locations together.

Fazza said:

OK. You can still use cell G25 - just make it the cell that controls the parameterised query.

I thought overnight of a better approach than the query table. Have a worksheet change event edit the pivot table's SQL on changes to cell G25.

Such as, create the pivot table in a new workbook (separate to the data workbook, to avoid memory leak problems). Once created, you can move the resultant worksheet back into the source data file. When creating the pivot table, take the external data option at the first page of the wizard. At the completed pivot tables, insert the code below - modify to suit - into the worksheet's code module.

Regards, Fazza

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$G$25" Then
    Application.EnableEvents = False
 
    Me.PivotTables(1).PivotCache.Sql = Join$(Array( _
        "SELECT *", _
        "FROM YourSourceDataReference", _
        "WHERE Colors Like '%' & '" & Target.Value2 & "' & '%'"), vbCr)
 
    Application.EnableEvents = True
  End If
End Sub

Click to expand...