Avatar of pdvsa
pdvsa
Flag 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

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Martin Liss

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 Liss

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

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....
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

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.
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
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pdvsa

ASKER
perfect.  Thank you very much Neeraj!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

You're welcome! Glad it worked as desired.