Forum

Notifications
Clear all

Goto Sheet

2 Posts
2 Users
0 Reactions
141 Views
(@mymalone)
Posts: 103
Estimable Member
Topic starter
 

I am trying to adjust my macro (listed below).  I would like for it to go to a specific sheet when I enter the name. However, when I enter a numeric title (i.e, 16R) it seems to go to sheet 16.  Can you tell me how to fix the problem?  Thanks

 

Sub GotoSheet()

' Macro2 Macro
' GoToSheet
'
Dim sSheet As String

sSheet = InputBox( _
  Prompt:="Sheet name or number?", _
  Title:="Input Sheet")
On Error GoTo noSheet
If Val(sSheet) > 0 Then
    Worksheets(Val(sSheet)).Activate
    Exit Sub
Else
    Worksheets(sSheet).Activate
    Exit Sub
End If

noSheet:
'Enter your code to display a warning that the sheet does not exist
'and/or bring up a selection box of all sheets
MsgBox "Sheet not found."
End Sub

 
Posted : 26/06/2018 11:51 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi Melinda

Try changing this line of codes

If IsNumeric(sSheet) Then
Worksheets(Val(sSheet)).Activate
Exit Sub
Else
Worksheets(sSheet).Activate
Exit Sub
End If

I suggest you don't use the sheet number as it can be very confusing. Use only the sheet's name instead.

I have attached an example of one that I did for another member some time ago.

Hope this helps.

Sunny

 
Posted : 26/06/2018 7:20 pm
Share: