Hyperlink
 

Hi,
i have a worksheet containing 12 sections (1 for each month of the year), is
there ary way i can put hyperlinks to each month on a seperate worksheet tab
so that when the hyperlink is clicked, the worksheet hides irrelevant month
rows & shows the relevent month ONLY onscreen?
--
Lois

Hyperlink
 

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


"Lois" wrote:

Hi,
i have a worksheet containing 12 sections (1 for each month of the year), is
there ary way i can put hyperlinks to each month on a seperate worksheet tab
so that when the hyperlink is clicked, the worksheet hides irrelevant month
rows & shows the relevent month ONLY onscreen?
--
Lois

Hyperlink
 

From your brief description I guess that you anticipate using 13 worksheets
in your Workbook. One for each month and one for the full year?
If this is the case then the easiest way to link the cells is to clink on
the cell where you want the data to be copied to, enter = then click on tab
of master worksheet then on cell containing the data you want linked. Press
enter. the resultant formula (in the month worksheet) should look something
like this =January!A1 (where January is the name on the worksheet tab). If
the month layout of your master worksheet is the same as the month worksheet
all you have to do is copy the formula to all relevant cells (clip & drag!).
Repeat for the remaining 11 months. Data entered on the master sheet will
automatically appear on the relevant month worksheet.
Hope this helps

"Lois" wrote:

Hi,
i have a worksheet containing 12 sections (1 for each month of the year), is
there ary way i can put hyperlinks to each month on a seperate worksheet tab
so that when the hyperlink is clicked, the worksheet hides irrelevant month
rows & shows the relevent month ONLY onscreen?
--
Lois