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
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
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
0
LD16Author Commented:
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.
0
LD16Author Commented:
Got it, I just need to enter the value in column b1.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LD16Author Commented:
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.
0
Ryan ChongCommented:
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
0
LD16Author Commented:
Thank you very much and concerning the range modification, is there a way to have used range instead of $A$1:$E$50000?
0
Ryan ChongCommented:
try this:

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:$E").AutoFilter Field:=1
                WS.Range("$A:$E").AutoFilter Field:=1, Criteria1:="=*" & v & "*", Operator:=xlAnd
            End If
        Next
    End If
End Sub

Open in new window

Check-logs-c.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.