Maybe the title is slightly confusing but I mean the following:
I've got a macro on the worksheet which filters the pivottable based on the value in cell B2.
When I put in a value that exists in the pivotfield it will show me that one, however if it doesn't exist it will show everything.
So the issue is: I want my pivottable to give me no results when the value in B2 doesn't match instead of everything.
The code on the worksheet is as following:
Any help would greatly be appreciated, I don't know much about VBA so it might be an easy fix!
I've got a macro on the worksheet which filters the pivottable based on the value in cell B2.
When I put in a value that exists in the pivotfield it will show me that one, however if it doesn't exist it will show everything.
So the issue is: I want my pivottable to give me no results when the value in B2 doesn't match instead of everything.
The code on the worksheet is as following:
VBA Code:
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("MacrotabelSA").PivotTables("DraaitabelSA")
Set xPFile = xPTable.PivotFields("itemprod")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub
Any help would greatly be appreciated, I don't know much about VBA so it might be an easy fix!