PivotTable filters based on cell value, if the value doesn't match it shows me everything

[Flux1234]

Hello all!

So I've got a VBA code on the worksheet. This basically says to filter the PivotTable based on the value in B2. However, when the value in B2 is blank OR does not match with anything, it will show me everything in the PivotTable.

Is there any way to change this to make it show nothing if the value in B2 doesn't match anything in the filter?

The code I used on the worksheet is the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set xPFile = xPTable.PivotFields("itemprod")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

I'm pretty clueless regarding VBA, so the solution might be really simple. Any help would be greatly appreciated!

[CK76]

In pivot table you must at least have 1 Label value selected. Otherwise it will throw error.

To work around this, you could add blank row to Pivot Table (i.e. add blank in source table) and use that when nothing is selected or selected value not found.

I'd recommend uploading desensitized sample workbook. It will make it so much easier for us to help you.

[Flux1234]

In pivot table you must at least have 1 Label value selected. Otherwise it will throw error.

To work around this, you could add blank row to Pivot Table (i.e. add blank in source table) and use that when nothing is selected or selected value not found.

I'd recommend uploading desensitized sample workbook. It will make it so much easier for us to help you.

Oh, that might be the issue then. Before I tried this I did it with a regular table and different code and that would show me only what I put in the cell, but that method would cause for more maintenance. I'm thinking about recording a macro to maybe automate the updating of that regular table, but if it's possible with the Pivot Table it would be a lot nicer.

The source is actually a Query from an ERP-system, so I don't think it would work to put in a blank there.

I made a desensitized sample workbook to make it a bit easier for anyone trying to help if theres still an alternate solution.

Thanks for the reply!

[CK76]

The source is actually a Query from an ERP-system

Are you using PowerQuery to connect to ERP-system and bring in data? If so, you can always append blank row to the query using M code.

If not, then I assume you are using querytable (i.e. legacy method like MS Query) to bring in data. And building Pivot Table from resulting table.

In that case, you can use code to load table data + 1 row into data model and use that to base your pivot table on.

Attached file is sample I created for merging two tables in memory and load to data model for creating pivot table.
You can skip merge portion and use that as basis for creating pivot table as needed.
By using something like... Range("AddressRange").Resize(Range("AddressRange").Rows.Count + 1 as source.

Though I'd strongly suggest using PowerQuery to bring in data, perform any necessary transformation, then load it directly to data model.

[Flux1234]

Are you using PowerQuery to connect to ERP-system and bring in data? If so, you can always append blank row to the query using M code.

If not, then I assume you are using querytable (i.e. legacy method like MS Query) to bring in data. And building Pivot Table from resulting table.

In that case, you can use code to load table data + 1 row into data model and use that to base your pivot table on.

Attached file is sample I created for merging two tables in memory and load to data model for creating pivot table.
You can skip merge portion and use that as basis for creating pivot table as needed.
By using something like... Range("AddressRange").Resize(Range("AddressRange").Rows.Count + 1 as source.

Though I'd strongly suggest using PowerQuery to bring in data, perform any necessary transformation, then load it directly to data model.

Thanks for the help. I'm pretty sure it's the second option yeah, not a PowerQuery. I tried making sense of your sample but I just really don't understand 99% of it to be honest.

It merges the 2 tables, and then creates a new pivot table?

Is it maybe possible to somehow make the Pivot Table add that + 1 row like you said without creating a new one?

I'm trying to keep it as simple as possible since it's for my thesis and I'll be gone from the company soon after so I can't make it too complex (not that I'm able to make something complex myself). Thanks for the help anyway. If what I asked doesn't have an easy solution (or at least not too someone who doesn't know a thing about VBA) maybe I'll just use the not-as-ideal solution I mentioned before.

[CK76]

Ok, here's mockup of concept.

I used dynamic named range as source for pivot table.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error GoTo ErrHandle:
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set xPFile = xPTable.PivotFields("itemprod")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    
ErrHandle:
    If Err.Number <> 0 Then
        xPFile.ClearAllFilters
        xPFile.CurrentPage = "(blank)"
    End If
    Application.ScreenUpdating = True
End Sub

[Flux1234]

Ok, here's mockup of concept.

I used dynamic named range as source for pivot table.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error GoTo ErrHandle:
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable1")
    Set xPFile = xPTable.PivotFields("itemprod")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    
ErrHandle:
    If Err.Number <> 0 Then
        xPFile.ClearAllFilters
        xPFile.CurrentPage = "(blank)"
    End If
    Application.ScreenUpdating = True
End Sub

Sorry for the late reply. I finally got it to work thanks to you! Thanks a lot for the help, I really appreciate it.