Macro to filter table by cell value, then loop for different cell values

[paul_sykes00]

Sheet 1 has a table of data in the rangeA2:I1000 which I wish to filter
Sheet 2, Column A has a list of filter values starting in Cell A2
“MacroX” is macro I wish to run after each loop



I'm trying to achieve the following

Loop 1
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A2” then run macro"X"

Loop 2
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A3” then run macro"X"

Loop 3
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A4” then run macro"X"

and so on until the macro has run through all filter values in “Sheet 2, ColumnA”

Hope you can help. Thanks in advance.

 

[Yongle]

To test
- put both procedures in same standard module
- run from Sheet1

Sub FilterMe()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range, cel As Range
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set rng2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
    Set rng1 = ws1.Range("A:A")
    
    For Each cel In rng2
        On Error Resume Next
        ws1.ShowAllData
        rng1.AutoFilter Field:=1, Criteria1:=cel
        Call MacroX("Filter value:" & vbCr & vbCr & cel)
    Next
End Sub

Private Sub MacroX(aStr)
    MsgBox aStr
End Sub

 

[Yongle]

Perhaps better if

Set rng1 = ws1.Range("A:A")

is altered to

Set rng1 = ws1.Range("A:[COLOR=#ff0000]I[/COLOR]")

To leave data unfiltered when macro is complete
- insert this after Next

ws1.ShowAllData

 

[paul_sykes00]

Perfect. Thanks very much for you help on this.

 

[Yongle]

thanks for your feedback

 

[paul_sykes00]

Would it be possible to add one more thing. On each loop the value of cell Z1 on worksheet 1 to match the filtered value for that loop. Thanks again

 

[Yongle]

If I understand correctly you want each value in sheet2 checked to see if it matches the value in Z1 ( presumably to allow you to run each value separately)
is that what you want?

If that is the case ...do you want the option (at run time) ...
... to choose the filter as EITHER Z1 value OR on all of sheet2 col A
(ie filter based on a single value OR run the current loop)

Additionally, if the value in Z1 is amended that implies requirement to run the macro filtered on Z1 value
- would it be useful to add a trigger to run the macro whenever value in Z1 changes with message box option allowing user to cancel

 

[paul_sykes00]

Hi Yongle,

No even more straight forward than that. So trying to add the bit in capitals

Loop 1
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A2”,
DISPLAY VALUE IN SHEET 2, CELL A2 IN SHEET 1, CELL Z1
then run macro"X"

Loop 2
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A3”
DISPLAY VALUE IN SHEET 2, CELL A3 IN SHEET 1, CELL Z1
then run macro"X"

Loop 3
Filter “Sheet 1, Column 1” by the value in “Sheet 2, Cell A4”
DISPLAY VALUE IN SHEET 2, CELL A4 IN SHEET 1, CELL Z1
then run macro"X"

and so on until the macro has run through all filter values in “Sheet 2, ColumnA”

 

[Yongle]

How about

Sub FilterMe()
    Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range, cel As Range
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set rng2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
    Set rng1 = ws1.Range("A:I")
    
    For Each cel In rng2
        On Error Resume Next
        ws1.ShowAllData
        rng1.AutoFilter Field:=1, Criteria1:=cel
        [COLOR=#ff0000]ws1.Range("Z1") = cel[/COLOR]
        Call MacroX
    Next
    ws1.ShowAllData
End Sub