Is there a possibility to put an OR while referencing a sheet name?

Question

I'm calling multiple workbooks and they all contain a sheet named either Desk or Desk_ or _Desk. I need to use data in these sheets to build pivots et cetera.

I am unable to use Sheets("Desk" or "Desk_" or "_Desk").Activate to reach these sheets.

Is there a way around this?

Answer 1

Use this function (which you can also pass a workbook variable to) to determine if a sheet exists (sourced from this solution):

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

---

You can call this function from your macro to dynamically assign your sheet. A few things to note here:

1. If more than one of your target sheets exist, the code will assign your sheet variable to the first one that hits a match
2. You may want to account for the possibility that none of the sheets exist

---

Sub Test()

Dim ws As Worksheet

'Dynamically assign worksheet variable
If WorksheetExists("Sheet1") Then
    Set ws = Sheets("Sheet1")
ElseIf WorksheetExists("Sheet2") Then
    Set ws = Sheets("Sheet2")
ElseIf WorksheetExists("Sheet3") Then
    Set ws = Sheets("Sheet3")
End If

'Account for none of the sheets existing
If ws Is Nothing Then
    MsgBox "None of sheets exist"
    Exit Sub
End If

MsgBox ws.Name

End Sub

Answer 2

You can also use formulas

to activate Sheet1 from workbook Food =HYPERLINK("[food.xlsx]Sheet1!$A$2","goto Food.Sheet1")

Get value of A2 if workbook Food is opened =[food.xlsx]Sheet1!$A$2

If not opened use its path ='C:\[food.xlsx]Sheet1'!$A$2