Copy/Paste Cell to Filter

SamanthaNG 0
Viewed 41

I am currently having an issue with a VBA code which i am unable to make it work. My idea was to have 2 sheets, the first sheet is with just a cell (Cell C7) to enter a customer code with a button "Click Here". The 2nd sheet (name: Volume) is a Pivot of all Data that is related to the customer codes (Volume, Net sale, Patterns, etc...). I wish to have a rule that can copy the number (customer code) that can be type in, and past to the 2nd sheet in the filter pivot field area (Cell A1), automatically it will filter and show the necessary data of that customer code. I've tried to do a Macro Recording, but it ended up fix on one Customer code only (see below):

Range("C7"). Select
Activesheet.pivotTables("Volume").PivotFields("Customer Code").ClearAllFilters
Activesheet.pivotTables("Volume").PivotFields("Customer Code").CurrentPage ="0001101073"
ActiveCell.Offset(0,1).Range("A1").Select
End Sub

As you can see the number of customer code is fix, so if i do type in another different number, it will automatically still filter the number that the Macro recorded.

I've then tried to change it, by putting everything in the same sheet, so the type in customer code cell is E3 and the pivot field filter cell is C3 by using this rule, but it still doesn't work (see below):

Sub Filter_Click()
    If Range("E3").Value = "" Then
        MsgBox ("You must enter the customer code.")
        Exit Sub
    End If
    
    With ActiveSheet.PivotTables("Volume").PivotFields("customer code").ClearAllFilters
         ActiveSheet.PivotTables("Volume").PivotFields("customer code").PivotFilters , Add:=xlValueEquals, Value1:=Range("C3").Value
    End With  
End Sub

I appreciate the kind help. Thank you so much.Regards.

pivot-table filter vba excel
1 Answers

You're almost there with your two attempts. Combining them gets you even closer. However you're using the value of C3, not E3 (where I think the code should be)

Try the following:

With ActiveSheet.PivotTables("Volume").PivotFields("customer code")
    .ClearAllFilters
    .CurrentPage = Range("e3").Value
End With

UPDATE:

If you want to error trap in the event of an invalid customer code being chosen - use this:

Sub Filter_Click()
    If Range("E3").Value = "" Then
        MsgBox ("You must enter the customer code.")
        Exit Sub
    End If
    On Error Resume Next
    With ActiveSheet.PivotTables("Volume").PivotFields("customer code")
        .ClearAllFilters
        .CurrentPage = Range("E3").Value
    End With
    If Err = 1004 Then
        MsgBox "Customer code not valid"
    Else
        Err.Raise Err
    End If
    On Error GoTo 0
End Sub
CLR0
Related