(VBA) Going to a worksheet with the name of a cell in another worksheet

[Bassie]

Hey everyone

I have searched the whole internet for this seemingly very simple problem (its probably not going to be simple at all)


So I have two workbooks:
workbook X with cell A2 with a name
And workbook Y with that same name (from A2 in X) as a worksheet name (multiple names/sheets get added so thats why i rather not have this hardcoded)

I am wondering if its possible to have a text in cell A2 in workbook X and then open workbook Y and have the sheetname with the text in A2 (X) open.

So for example A2 in Workbook Y = Coffee
So it will go to workbook X and go to the sheet thats called Coffee

I hope I made it easy to understand. Sorry if i caused any confusion and many thanks in advance!

 

[BSALV]

Sub Bassie()
     Dim WB As Workbook, sh As Worksheet, WBY, sshName

     myworkbooky = "C:\Users\Eigenaar\Downloads\koffie.xlsx"    'workbook Y with path
     sp = Split(myworkbooky, "\")
     WBY = sp(UBound(sp))                                       'workbooky name

     With ThisWorkbook.Sheets("blad3").Range("A1")              '--> the cell with the name of the sheet in workbook Y
          If Len(.Value) = 0 Then MsgBox "sorry", vbCritical: Exit Sub
          sshName = CStr(.Value)                                'convert to a string, in case it was a number
     End With

     ' If IsError(Evaluate("ISREF('[" & wby & "]" & sshname & "'!$A$1)")) Then     'test of workbook Y is already open and the sheet accessible
     On Error Resume Next
     Windows(WBY).Activate                                      'try to go to the workbook Y
     If Err.Number = 9 Then                                     'no succes
          Err.Clear
          Set WB = GetObject(myworkbooky)                       'open workbook Y
          Windows(WBY).Activate                                 'should now be open, try to workbook Y
          If Err.Number = 9 Then MsgBox "can't open " & WBY, vbCritical: Exit Sub     'still not ok = end of story
     End If

     Set sh = Sheets(sshName)                                   'now try referring to the sheet
     If sh Is Nothing Then MsgBox "no sheet " & sshName, vbCritical: Exit Sub     'that sheetname doesn't exist
     On Error GoTo 0

     Application.Goto Sheets(sshName).Range("A1")               'goto that cell in your sheet

End Sub

 

[Bassie]

Sub Bassie()
     Dim WB As Workbook, sh As Worksheet, WBY, sshName

     myworkbooky = "C:\Users\Eigenaar\Downloads\koffie.xlsx"    'workbook Y with path
     sp = Split(myworkbooky, "\")
     WBY = sp(UBound(sp))                                       'workbooky name

     With ThisWorkbook.Sheets("blad3").Range("A1")              '--> the cell with the name of the sheet in workbook Y
          If Len(.Value) = 0 Then MsgBox "sorry", vbCritical: Exit Sub
          sshName = CStr(.Value)                                'convert to a string, in case it was a number
     End With

     ' If IsError(Evaluate("ISREF('[" & wby & "]" & sshname & "'!$A$1)")) Then     'test of workbook Y is already open and the sheet accessible
     On Error Resume Next
     Windows(WBY).Activate                                      'try to go to the workbook Y
     If Err.Number = 9 Then                                     'no succes
          Err.Clear
          Set WB = GetObject(myworkbooky)                       'open workbook Y
          Windows(WBY).Activate                                 'should now be open, try to workbook Y
          If Err.Number = 9 Then MsgBox "can't open " & WBY, vbCritical: Exit Sub     'still not ok = end of story
     End If

     Set sh = Sheets(sshName)                                   'now try referring to the sheet
     If sh Is Nothing Then MsgBox "no sheet " & sshName, vbCritical: Exit Sub     'that sheetname doesn't exist
     On Error GoTo 0

     Application.Goto Sheets(sshName).Range("A1")               'goto that cell in your sheet

End Sub

Genius!! thank you so much!

 

[Bassie]

Sub Bassie()
     Dim WB As Workbook, sh As Worksheet, WBY, sshName

     myworkbooky = "C:\Users\Eigenaar\Downloads\koffie.xlsx"    'workbook Y with path
     sp = Split(myworkbooky, "\")
     WBY = sp(UBound(sp))                                       'workbooky name

     With ThisWorkbook.Sheets("blad3").Range("A1")              '--> the cell with the name of the sheet in workbook Y
          If Len(.Value) = 0 Then MsgBox "sorry", vbCritical: Exit Sub
          sshName = CStr(.Value)                                'convert to a string, in case it was a number
     End With

     ' If IsError(Evaluate("ISREF('[" & wby & "]" & sshname & "'!$A$1)")) Then     'test of workbook Y is already open and the sheet accessible
     On Error Resume Next
     Windows(WBY).Activate                                      'try to go to the workbook Y
     If Err.Number = 9 Then                                     'no succes
          Err.Clear
          Set WB = GetObject(myworkbooky)                       'open workbook Y
          Windows(WBY).Activate                                 'should now be open, try to workbook Y
          If Err.Number = 9 Then MsgBox "can't open " & WBY, vbCritical: Exit Sub     'still not ok = end of story
     End If

     Set sh = Sheets(sshName)                                   'now try referring to the sheet
     If sh Is Nothing Then MsgBox "no sheet " & sshName, vbCritical: Exit Sub     'that sheetname doesn't exist
     On Error GoTo 0

     Application.Goto Sheets(sshName).Range("A1")               'goto that cell in your sheet

End Sub

Hey,

Again the code helps me a ton. I love it when I filled in the wrong name and I got an error that I filled in the wrong name instead of a general error.

Sometimes when I run the code though I get a "User-defined type not defined" what can this be?

Many thanks again!

 

[BSALV]

perhaps you used illegal characters ???
Can you give an example.

Characters to Avoid in Filenames and Directories | Michigan Tech

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.

www.mtu.edu

 

[Bassie]

perhaps you used illegal characters ???
Can you give an example.

Characters to Avoid in Filenames and Directories | Michigan Tech

Your web files will be viewed by numerous users who use a wide variety of operating systems and devices. It is essential to avoid common illegal directory and filename characters.

www.mtu.edu

Hey I think it was because I used the button wrong that I pasted the code under. It works fine now though! thanks again!!