Search multiple spreadsheets and return value

[Ryan1uk]

Hi,

Hope you are all having a lovely day and staying safe. I recently downloaded a template spreadsheet and wanted to change it a little. I have only started learning Excel so please excuse me, I would have searched but don't really know what to search for and if it's even possible.

I have 11 spreadsheets open in a workbook.

Dashboard, Name, Name (2), Name (3) etc

• First I need to get the name from Dashboard D2
• Then search across the 10 spreadsheets for that name which will appear in Name B9, Name (2) B9, Name (3) B9 etc
• Go across the column to Name AB9, Name (2) AB9, Name (3) AB9 etc
• Look down the column and if there is something in the cell return some text to Dashboard J2

Any help is much appreciated and have a good day.

Ryan

 

[mumps]

Try:

Sub SearchSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet, rng As Range, firstRow As Long, lastRow As Long, fnd As Range
    Set desWS = Sheets("Dashboard")
    With desWS
        lastRow = .Cells(Cells(1, "A").End(xlDown).Row, "A").Row
        .Range("J2:J" & lastRow).ClearContents
    End With
    With desWS
        For Each rng In .Range("D2:D" & lastRow)
            For Each ws In Sheets
                 If ws.Name <> "Dashboard" Then
                    Set fnd = ws.Range("B:B").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
                    If Not fnd Is Nothing Then
                        desWS.Cells(rng.Row, 10) = ws.Range("AB" & fnd.Row + 1)
                        Exit For
                    End If
                End If
            Next ws
        Next rng
    End With
    Application.ScreenUpdating = True
End Sub

 

[Ryan1uk]

WOW, now that works perfectly. Just wanted to say thanks again I really appreciate your time and effort. Hope you and your family stay safe and like I said if you are ever in Norwich, England I owe you a pint

 

[mumps]

You are very welcome and you stay safe as well.