VBA to apply Filter accross multiple sheets with the same range

[Ramadan]

I'm trying to use Filter function to collect data from the same ranges in multiple sheets based on match with "Parial Text " in cell "$B$3" but it seems that Filter Function doesn't work for multiple sheets while when testing the formula for each sheet individually it works smoothly. this is my formula for 2 sheets only but it gives me only the first sheet data

=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K)))+(ISNUMBER(SEARCH(B3,District1!M:M))),FILTER('Carnell'!B:D,(ISNUMBER(SEARCH(B3,'Carnell'!G:G)))+(ISNUMBER(SEARCH(B3,'Carnell'!I:I)))+(ISNUMBER(SEARCH(B3,'Carnell'!K:K)))+(ISNUMBER(SEARCH(B3,'Carnell'!M:M)))))

It's to collect data Col "B:D" from each sheet if cel "$B$3" = any cell in columns "G,I,K,M" at any sheet. All sheets has the same table and same format just different data and I need to repeat this function 7 times for 7 sheets
I've been searching long time to solve such issue but only found that it can be done with VSTACK function which is not available in my office version 2021

Is there any suggestions to do this through VBA ??

 

[Ramadan]

I have found a great code to search accors multiple sheets better than filter posted by @Joe Was on 2008 and it works very good with different options but for me there is one problem that i need to fix it
When I select the option to copy the found data to my sheet "test", the code doesn't list the found data underneath each other but all in the same row "B" and the new data delete the last found one which means if there are 10 results matching the search, I will get only the last found one
and also if I'm searching Arabic words the excel freezes for a long time
is there any suggestions please to fix that
here is the code

Public Sub FindText()
'Run from standard module, like: Module1.
'Find all data on all sheets!
'Do not search the sheet the found data is copied to!
'List a message box with all the found data addresses, as well!
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
'Do not search sheet4!
If ws.Name = "test" Then GoTo myNext
If ws.Name = "Stop Work" Then GoTo myNext

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address

Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf

Set Found = .UsedRange.FindNext(Found)

'Copy found data row to sheet4 Option!
 Found.EntireRow.Copy _
 Destination:=Worksheets("test").Range("A65536").End(xlUp).Offset(1, 0)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If

myNext:
End With

Next ws

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:

MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub