Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Filtering VBA - Add Criteria

Experts,

How can I add a criteria "Pending"  on line 23?  
I want to have 2 filters:  Issued and Pending

thank you


Sub Sort_LC()
'
' Sort_LC Macro
'

'
    Columns("A:AA").Select
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("M2:M20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("G2:G20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("B2:B20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LC").Sort
        .SetRange Range("A1:AA20225")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        
    End With
    ActiveSheet.Range("$A$1:$AA$2150").AutoFilter Field:=1, Criteria1:= _
        "=Issued", Operator:=xlOr, Criteria2:="="
    Range("A1").Select
    Sheets("pivot").Select
    ActiveSheet.PivotTables("Pivot_Banks_With_LC").PivotSelect "Status", xlButton, _
        True
    ActiveSheet.PivotTables("Pivot_Banks_With_LC").PivotCache.Refresh
    Sheets("LC").Select
    Range("A1").Select
End Sub

Open in new window

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Change line 2 as desired.
Sub SelectPivot()
Sort_LC "Pending"
End Sub

Open in new window


Sub Sort_LC(strWhich As String)
'
' Sort_LC Macro
'

'
    Columns("A:AA").Select
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("M2:M20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("G2:G20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("LC").Sort.SortFields.Add Key:=Range("B2:B20225"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LC").Sort
        .SetRange Range("A1:AA20225")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        
    End With
    ActiveSheet.Range("$A$1:$AA$2150").AutoFilter Field:=1, Criteria1:= _
        "=" & strWhich, Operator:=xlOr, Criteria2:="="
    Range("A1").Select
    Sheets("pivot").Select
    ActiveSheet.PivotTables("Pivot_Banks_With_LC").PivotSelect "Status", xlButton, _
        True
    ActiveSheet.PivotTables("Pivot_Banks_With_LC").PivotCache.Refresh
    Sheets("LC").Select
    Range("A1").Select
End Sub

Open in new window

Or change the first sub to

Sub SelectPivot()
Dim strWhich As String
strWhich = InputBox("Whick pivot table?", "Choose Pivot Table", "Status")
If strWhich = "Status" Or strWhich = "Pending" Then
    Sort_LC strWhich
Else
    MsgBox "Must be 'Status' or 'Pending"
End If
End Sub

Open in new window

Avatar of pdvsa

ASKER

HI Martin,

apologies but I am not much of a coder.
I do not follow.

If could kindly let me know how to adjust only the posted code.  
I was thinking only line 23 would be changed.

thank you....
Well yes, you could manually change that line but I assume you don't want to do that, so in this post of mine I provided a modified version of your Sort_LC sub which accepts a parameter that tells the sub the name of the table you want to use, and this post provides a sub that you would call instead of Sort_LC. I'm happy to answer further questions.
Avatar of pdvsa

ASKER

Hi Martin,
Thank you
<Well yes, you could manually change that line but I assume you don't want to do that,
==>i do wish to only change that line.  

Thank you so much...

ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pdvsa

ASKER

perfect.  Thank you very much Neeraj!
You're welcome! Glad it worked as desired.