Thread: Macro to search for data in workbooks

I have a macro that searches for data in worksheets data1 - data5 in the same workbook.
How to change the macro to the data searched in all the worksheets and workbooks ?

My Marco:

Sub Search()
    Dim xlForm As Excel.Worksheet, ostForm As Long
    Dim xlWks As Excel.Worksheet, ostDane As Long
    Dim arWks As Variant: arWks = VBA.Array("Data", "Data2", "Data3", "Data4", "Data5")
    Dim i As Integer
   
    Dim RngS          As Excel.Range
    Dim RngCrit       As Excel.Range
   
    Set xlForm = ThisWorkbook.Worksheets("Search")
    With xlForm
        ostForm = Last(.Columns("A:J"))
        If ostForm > 8 Then .Range("A8:J" & ostForm).Clear
        Set RngCrit = .Range("A1").CurrentRegion
    End With
   
    Application.ScreenUpdating = False
   
    For i = 0 To UBound(arWks)
        ostForm = Last(xlForm.Columns("A:J")) + 1
       
        Set RngS = ThisWorkbook.Worksheets(CStr(arWks(i))).Range("A2").CurrentRegion
        RngS.AdvancedFilter Action:=xlFilterCopy, _
                            CriteriaRange:=RngCrit, _
                            CopyToRange:=xlForm.Range("A" & ostForm), _
                            Unique:=False

        xlForm.Rows(ostForm).Delete
    Next
   
    Application.ScreenUpdating = True
   
    Set RngS = Nothing
    Set xlForm = Nothing
End Sub

regards
Kiluk

Of course you're right.
I added attachments with sample data and files to search.
The file "search" and two data files.
I would like to file a macro in the file "search" searched other data files and focused search results in a worksheet
so how does it work in a workbook.

kiluk