We help IT Professionals succeed at work.

Filtering VBA - Add Criteria

pdvsa
pdvsa asked
on
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

Comment
Watch Question

View Solution Only

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

pdvsaProject finance

Author

Commented:
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....
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
pdvsaProject finance

Author

Commented:
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...

Excel & VBA Expert
SILVER EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
If you want to filter the range on two values "Issued" and "Pending", the line#23 should be changed to this... 

ActiveSheet.Range("$A$1:$AA$2150").AutoFilter Field:=1, Criteria1:= _
        "=Issued", Operator:=xlOr, Criteria2:="=Pending"

pdvsaProject finance

Author

Commented:
perfect.  Thank you very much Neeraj!
Subodh Tiwari (Neeraj)Excel & VBA Expert
SILVER EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked as desired.