VBA Hide/Unhide Worksheets - Runtime Error

[delaney1102]

Hi all,
I have a workbook that's combining time off calendars for a couple departments with hyperlinks to hidden worksheets (months) for each department.

This is what I have written on the Navigation tab

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
MySheet = Left(LinkTo, WhereBang - 1)
Worksheets(MySheet).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(MySheet).Range(MyAddr).Select
End If


End Sub


The macro works great for the first group of months, but when I click on say June/July/August or whatever in the second group of months, I get a Runtime Error 9 message and the line that is highlighted when I debug is:

Worksheets(MySheet).Visible = True


The second group of months are named "June (2)", "July (2)", etc. I thought that could that be my problem but when I tried renaming the second group of months but then nothing worked, not even the first group of hyperlinks.

Any help would be appreciated!!

 

[Fluff]

Hi & welcome to MrExcel.
Try

MySheet = Replace(Left(LinkTo, WhereBang - 1), "'", "")

 

[Norie]

When you get the error have you checked that MySheet contains a value that's a valid sheet name in the active workbook?

 

[delaney1102]

Thank you!! Been coming here forever for tips, this is my first post though

where should i add that in?

 

[Fluff]

Just replace this line

MySheet = Left(LinkTo, WhereBang - 1)

with my suggestion.

 

[delaney1102]

you're awesome!! thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback