Can't paste when using method to filter a Pivottable based on cell value

[Flux1234]

Hello all!

So I've got a worksheet where I filter the table based on a cell value. I use a macro on another worksheet to paste a certain value in that cell, so the table filters it.

However, this is a workaround since the information of the table comes from a PivotTable, so to update the information in the table it's a hassle. That's why I'm trying to get the PivotTable filtered on a cell value. As I don't know that much about VBA I've mainly been looking on the internet for codes.

I got the following one to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'This line stops the worksheet updating on every change, it only updates when cell
'B2 or B3 is touched
If Intersect(Target, Range("B2:B3")) Is Nothing Then Exit Sub
 
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
 
'Here you amend to suit your data
Set pt = Worksheets("MacrotabelSA").PivotTables("DraaitabelSA")
Set Field = pt.PivotFields("itemprod")
NewCat = Worksheets("MacrotabelSA").Range("B2").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
End With
 
End Sub

But, the issue is now that it often doesn't work, the macro from the other worksheet to paste the value in the cell which updates the pivottable gets an error when pasting. Getting the error:
Error 1004: PasteSpecial method of range class failed

I'd say it works about 50% of the times but I can't find out why it does or doesn't work.

The macro used to paste the value is:

Sub Refresh()
'
' Refresh Macro
'

'

    Range("J6").Copy
    Sheets("MacrotabelSA").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A11:A18").Select
    Selection.Copy
    Sheets("Info DB").Select
    Range("E5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Since the pasting issue wasn't there when I filtered the regular table, I'm quite sure something in the code of the worksheet is blocking it or something. But since I'm really inexperienced on the subject I wouldn't really know.

If anyone knows how I could fix this it would be greatly appreciated. I'm also open to completely different codes on the worksheet for filtering the PivotTable but I've tried some but couldn't get them to work except for this one.

Thanks!

 

[Flux1234]

After struggling with it for 1.5 day I find the solution in another code I've tried before 10 minutes after posting here. Can't seem to edit/remove my post though.

 

[Flux1234]

Actually, a new problem has risen.

My code on the worksheet is:

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

But because I'm pasting empty values on it as well, I need it to not remove filter when a blank or 0 gets pasted but instead have the PivotTable show no results. It sounds pretty easy but I can't figure it out yet. Does anyone know?

 

[Flux1234]

Sorry for spamming but I can't edit my post. The above issue is not just blank or 0, every value that isn't in the filterlist that I put in B2 is making it show everything. So what I need is that the input of filter exactly matches B2 and if it doesn't match, it shouldn't show anything in the pivottable.