Switching tabs - VBA code

[aymericdv]

Private Sub Macro12()
Dim SheetNo As String

SheetNo = Worksheets("Select Sheet").Range("B2").Text

If Worksheets("Select Sheet").Range("B2") <> "" Then
Worksheets("SheetNo").Activate
Range("A1").Activate

End If

End Sub

Can anyone tell me why the above does not work?

I am trying to acivate the relevant worksheet using a button linked to "Macro12".

In B2 of the sheet "Select Sheet", I have data validated the cell to give me the names of 7 other worksheets (Sheet1 - Sheet7).

MsgBox SheetNo = Worksheets("Select Sheet").Range("B2").Text gives Sheet3 if for example in cell B2 I have chosen Sheet3.

I get an error from the line coloured in red above.

Thanks in advance
Aymeric

 

[Jonmo1]

In this line
Worksheets("SheetNo").Activate

It's not actually using the SheetNo as a variable
Because of the Quote marks, it's literally looking for a sheet named "SheetNo"
Not whatever the value of the Variable SheetNo

Try
Worksheets(SheetNo).Activate

 

[aymericdv]

Thanks, I could swear I tried that but obviously not as it works!

 

[Jonmo1]

You're welcome.