You can't do all you want with hyperlinks. You could go to a particular cell
on the other sheet, but you can't hide/unhide rows through it. At least not
directly.
The solution below uses a Double-Click on one of your cells that you would
have had a hyperlink in to accomplish the task. You'll need to know the rows
that each section uses when setting up the code also. This crude, but works
to show that it can be done. If the rows per month section is going to
change over time, then you'd want a more robust way of figuring it out so
that the code can do that also.
To get this code into the workbook, select the sheet you planned to have the
hyperlinks on and right-click its name tab and choose [View Code] from the
popup list that appears. Copy and paste the code below into that module.
Change the name of the sheet (now Sheet2) to be the name of the sheet with
the information you want to hide/show when you double-click a "link" cell in
this sheet. Also change the row numbers assigned to the array elements for
the 12 months in array monthRows(). This all set up so that your "link"
cells are A2 through A13 (January through December) on the main sheet.
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
'change the name in the next instruction to the
'name of the sheet with the information to show/hide
Const gotoSheetName = "Sheet2"
Dim monthRows(1 To 12, 1 To 2) As Integer
Dim LC As Integer ' loop counter
Dim sheetToShow As Worksheet
'define constants that tell which rows are associated
'with which months, keeping them in an array for easy use
'set up contents of monthRows()
monthRows(1, 1) = 1 ' Jan starts at row 1
monthRows(1, 2) = 31 ' Jan ends at row 31
monthRows(2, 1) = 32 ' Feb starting row
monthRows(2, 2) = 58 ' Feb ending row
monthRows(3, 1) = 59 ' March starting row
monthRows(3, 2) = 89 ' March ending row
monthRows(4, 1) = 90 ' April starting row
monthRows(4, 2) = 119 ' April ending row
monthRows(5, 1) = 120 ' May starting row
monthRows(5, 2) = 150 ' May ending row
monthRows(6, 1) = 151 ' June starting row
monthRows(6, 2) = 180 ' June ending row
monthRows(7, 1) = 181 ' July starting row
monthRows(7, 2) = 211 ' July ending row
monthRows(8, 1) = 212 ' Aug starting row
monthRows(8, 2) = 242 ' Aug ending row
monthRows(9, 1) = 243 ' Sep starting row
monthRows(9, 2) = 272 ' Sep ending row
monthRows(10, 1) = 273 ' Oct starting row
monthRows(10, 2) = 303 ' Oct ending row
monthRows(11, 1) = 304 ' Nov starting row
monthRows(11, 2) = 333 ' Nov ending row
monthRows(12, 1) = 334 ' Dec starting row
monthRows(12, 2) = 364 ' Dec ending row
'make sure we are dealing with
'a double-click in Column A
'and in rows 2 through 13 (Jan-Dec)
Select Case Target.Address
Case Is = "$A$2" ' January
monthNumber = 1
Case Is = "$A$3" ' February
monthNumber = 2
Case Is = "$A$4" ' March
monthNumber = 3
Case Is = "$A$5" ' April
monthNumber = 4
Case Is = "$A$6" ' May
monthNumber = 5
Case Is = "$A$7" ' June
monthNumber = 6
Case Is = "$A$8" ' July
monthNumber = 7
Case Is = "$A$9" ' August
monthNumber = 8
Case Is = "$A$10" ' September
monthNumber = 9
Case Is = "$A$11" ' October
monthNumber = 10
Case Is = "$A$12" ' November
monthNumber = 11
Case Is = "$A$13" ' December
monthNumber = 12
Case Else
'we don't care - Do NOTHING
Exit Sub
End Select
Set sheetToShow = _
ThisWorkbook.Worksheets(gotoSheetName)
For LC = LBound(monthRows) To UBound(monthRows)
If LC = monthNumber Then
'this is the month we want to show
sheetToShow.Rows(monthRows(LC, 1) & ":" & _
monthRows(LC, 2)).EntireRow.Hidden = False
Else
'this is a month we want to hide
sheetToShow.Rows(monthRows(LC, 1) & ":" & _
monthRows(LC, 2)).EntireRow.Hidden = True
End If
Next
sheetToShow.Activate
Application.Goto sheetToShow.Range("A" & _
monthRows(monthNumber, 1)), scroll:=True
Set sheetToShow = Nothing
End Sub
