asked on

VBA: filter multiple data from different sheets

Hello experts,

I have two sheets: log and request.

The two sheets have the same field and position (Column A) I would like to report a value in my runner sheet and be able to filter with the content of B1 cell.

Please find attached the example.

In my example I just putted two sheets, the idea is to perform the filter two the various sheets <> than Runner as all the sheets will have the same Column A (Request#)

Thank you again for your help.
Check-logs.xlsm

Ryan Chong

try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 2 Then
        v = Cells(Target.Row, Target.Column)
        Worksheets("CCWT_logs").Range("$A$1:$E$50000").AutoFilter Field:=1
        Worksheets("CCWT_logs").Range("$A$1:$E$50000").AutoFilter Field:=1, Criteria1:="=*" & v & "*", Operator:=xlAnd
        
        Worksheets("CCWT_requests").Range("$A$1:$E$50000").AutoFilter Field:=1
        Worksheets("CCWT_requests").Range("$A$1:$E$50000").AutoFilter Field:=1, Criteria1:="=*" & v & "*", Operator:=xlAnd
    End If
End Sub

Open in new window

once value at Runner's B1 is changed, the another worksheets will be auto-filtered.
Check-logs-b.xlsm

Luis Diaz

ASKER

Thank you very much.

How should I run this as it is a private sub should I add another code?

Concerning the range, why you have fixed rows count is not possible to have usedrange rows.count?

Thank you again for your help.

Luis Diaz

ASKER

Got it, I just need to enter the value in column b1.

Luis Diaz

ASKER

Now if I add another sheet, instead of reporting the sheets is not a way to make a for each sheets <> runner make the filter?

Thank you againi for your help.

Ryan Chong

try like:

Private Sub Worksheet_Change(ByVal Target As Range)

dim WS as worksheet

If Target.Row = 1 And Target.Column = 2 Then v = Cells(Target.Row, Target.Column)

for each WS in worksheets

If lcase(WS.name) <> "runner" then
WS.Range("$A$1:$E$50000").AutoFilter Field:=1 WS.Range("$A$1:$E$50000").AutoFilter Field:=1, Criteria1:="=*" & v & "*", Operator:=xlAnd
End if

Next

End If

End Sub

Luis Diaz

ASKER

Thank you very much and concerning the range modification, is there a way to have used range instead of $A$1:$E$50000?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

It works, thank you very much for your help.

I have create another question related to this one as I want to filter the request not just based on the information of Range("b1") but also based on the information reported in column B.