This will add hyperlinks on sheet1 to each of the following sheets, 2, 3, 4,
etc. The hyperlink text will be the sheet name, the cell under it will be
the value from cell A7 on that sheet. The sheet names will begin on sheet1,
range D8 and continue for each column to the right for as many sheets you
have.

Sub test()
Dim i As Long
Dim DataClmn As Long
Dim ShtName As String

DataClmn = 4
For i = 2 To Worksheets.Count
ShtName = Worksheets(i).Name

With Worksheets(1)
.Hyperlinks.Add Anchor:=.Cells(8, DataClmn), _
Address:="", _
SubAddress:=ShtName & "!A7", _
ScreenTip:="", _
TextToDisplay:=ShtName & ""
.Cells(9, DataClmn).Value = Worksheets(i).Range("A7").Value
End With
DataClmn = DataClmn + 1

Next i
End Sub

Mike F
wrote in message
ups.com...
I am creating a simple envelope budgeting sheet. On my first sheet, I
would like to have a list of all the other sheets ('envelopes') and
their balance. Each 'envelope' sheet has its current balance in cell
A7 and I would like to access that as well.

So my first sheet would have something like:

8 | 9
D <name of second sheet| value of A7 on second sheet
E <name of third sheet | value of A7 on third sheet
...

If possible, I would like this to be automatic so when I add another
envelope sheet, its name and A7 value would show up on the first sheet.

Finally, would it be possible to detect a double click on the sheet
name and
then switch to that sheet?

Thanks in advance for any help.