how to activate a workbook from another active workbook without the filename?

[jbstream09]

Hi,
I am new to VBA, is there a way to activate a workbook from another active workbook without the filename, and searching by the sheet1 instead?

I currently have; Windows("123456789.xls").Activate ,which works fine, however the file name constantly changes (always 9 characters) and I would prefer to activate the workbook by the tab name sheet1 instead.

Current active workbook I have:

Sub TEST6()

With Worksheets("Untitled").Columns(11)
.NumberFormat = "0"
.Value = .Value
End With

Range("A1").Select
ActiveWorkbook.Worksheets("Untitled").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Untitled").Sort.SortFields.Add Key:=Range( _
"A2:A65536"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Untitled").Sort
.SetRange Range("A1:L65536")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Windows("123456789.xls").Activate

 

[JLGWhiz]

If you only have two workbooks open then you could use something like this

If Workbooks(1).Name = ActiveWorkbook.Name Then
    Workbooks(2).Activate
    Sheets(1).Activate
Else
    Workbooks(1).Activate
    Sheets(1).Activate
End If

 

[RobertSF]

Hi, and welcome to the forum!

You can't open a workbook without its file name. To do what you want, you would have to open all workbooks with nine-character names and see if they have a sheet with the name you're looking for. Compounding the problem, what if two spreadsheets have the sheet name you're looking for? But if you expect unique sheet names, this should work.

Option Explicit

Sub test()
    Debug.Print FindBySheetName("bingo", "H:\")
End Sub


Function FindBySheetName(strSheetName As String, strPath As String) As String
    
    Dim ndx As Long
    Dim strFileName As String
    
    strFileName = Dir$(strPath & "\" & "?????????.xlsx")
    
    While strFileName <> ""
        Workbooks.Open (strPath & "\" & strFileName)
        For ndx = 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(ndx).Name = strSheetName Then
                FindBySheetName = ActiveWorkbook.Name
                ActiveWorkbook.Close
                Exit Function
            End If
        Next ndx
        ActiveWorkbook.Close
        strFileName = Dir$
    Wend
    
    FindBySheetName = "Not Found"

End Function

Most of the work happens in the while loop. We open every workbook in the given path and check for the sheet name we're looking for. If it finds it, it returns the workbook name, and if not, it returns "Not Found."

Note that you still have to open the workbook once it's found because the function, for the sake of leaving things the way they were, closes the workbook after determining it's the correct one.

Note as well that the function can be made fancier, making it return the actual workbook object instead of just its name string, but the above should get you started in that direction.

I hope that helps!

EDIT: Oops, I didn't consider the workbooks might be open already. If that's the case, then @JLGWhiz answer is better. If there are more than two open workbooks, adapt my code to cycle through them, looking for the one with the specified sheet.