how do i create a macro to select a specific worksheet using a value in my active worksheet.

[Eddie Phan]

Hi All,
I am trying to assign a macro to a button to select a worksheet based on the value on my active worksheet.
e.g. The value on cell "B4" on the active worksheet is "GL1500", i am trying to assigned a macro to select Worksheet Name "GL1500" out of the multiple worksheet of GL1300 to GL2000. Therefore once i run the macro, it go to worksheet name GL1500 and become my active worksheet.

I am wondering can this be done with vba?

Thank in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

I am wondering can this be done with vba?

Sure, try this.
It assumes that B4 will definitely contain a name of one of the worksheets in the workbook.

Sub Go_To_Sheet()
  Sheets(Range("B4").Value).Activate
End Sub

 

[My Aswer Is This]

I would try this:

Private Sub CommandButton1_Click()
'Modified  3/23/2021  5:03:21 AM  EDT
On Error GoTo M
Dim ans As String
ans = Range("B4").Value
Sheets(ans).Activate
Exit Sub
M:
MsgBox "There is no sheet named" & vbNewLine & ans, , "Oops"
End Sub

 

[My Aswer Is This]

If you wanted to do this in any sheet in your workbook without needing a button.
Try this:
The script is activated when you enter any value in Range("B4") in any sheet in your workbook.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
In top left corner of window double click on This Workbook
Paste the code in the VBA edit window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
    If Target.Address = Range("B4").Address Then Sheets(Target.Value).Activate
    
Exit Sub
M:
MsgBox "There is no sheet named" & vbNewLine & ans, , "Oops"
End Sub