VBA Activate a sheet from another Workbook based on Cell Value

[decadence]

Hi I have a lookup workbook with 50+ sheets of data and I would like to activate a specific work sheet from that workbook if a cell in the active sheet containing a specific value matches the sheet name, Is this possible to do this?

 

[decadence]

Basically There will be multiple different workbooks of data that will contain one of these words in the array but not sure which one as there is varying rows of data in every workbook with up to 10,000 rows of data per workbook, hence trying to match the word against the lookup workbook would make it a lot easier and faster to do

 

[mumps]

The way this macro is written, it would have to be copied into a regular module in every different workbook of data that will contain one of the search words and each workbook would have to be saved as a macro-enabled file. The macro would then be run from each workbook. Change the names in the array to match the words that you want to search for.

Sub SelectSheet()
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Dim myArray As Variant
    myArray = Array("Word1", "Word2", "Word3", "Word4")
    Dim i As Long
    For i = LBound(myArray) To UBound(myArray)
        Set foundVal = ActiveSheet.UsedRange.Find(myArray(i), LookIn:=xlValues, lookat:=xlWhole)
        If Not foundVal Is Nothing Then
            Workbooks("Data Lookup 2018.xlsx").Activate
            Workbooks("Data Lookup 2018.xlsx").Sheets(foundVal.Value).Select
            Exit For
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[decadence]

Thank you mumps this is exactly what I need, much appreciated as I have been struggling to get my head round this one for a while.

 

[mumps]

You are very welcome.