Get other sheet names into a column

[Guest]

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.

 

[Guest]

i am sure that some vba cose could make each new sheetname automatically add
to your list but in the meantime try this.Type the name of each sheet in a
column and in the next cell to the right type =,navigate to A7 on that sheet
and hit enter.Now make your name into a hyperlink to that sheet.Check out
HYPERLINK in help,there are examples on jumping to worksheets at he bottom of
the page.So you have the name of each worksheet act as a hyperlink to that
worksheet with the value od A7 on that worksheet shown next to it.

paul
remove nospam for email addy!

 

[William]

Hi Keyser

Assuming your summary sheet is called "Index" try..


Sub Test()
Dim ws As Worksheet, c As Range
With ThisWorkbook.Sheets("Index")
..Cells.ClearContents
..Range("A1") = "Index"
For Each ws In Worksheets
If Not ws.Name = "Index" Then _
..Range("A65000").End(xlUp).Offset(1, 0) = ws.Name
Next ws
..Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
For Each c In _
..Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Offset(0, 1)
c.FormulaR1C1 = "=INDIRECT(""'"" &RC[-1]&""'!A7"")"
..Hyperlinks.Add Anchor:=c.Offset(0, -1), Address:="", _
SubAddress:="'" & c.Offset(0, -1) & "'!A7"
Next c
End With
End Sub

Alternatively, you could place similar code into the worksheet module so the
list is created when you activate the sheet.....

Private Sub Worksheet_Activate()
Dim ws As Worksheet, c As Range
With ThisWorkbook.Sheets("Index")
..Cells.ClearContents
..Range("A1") = "Index"
For Each ws In Worksheets
If Not ws.Name = "Index" Then _
..Range("A65000").End(xlUp).Offset(1, 0) = ws.Name
Next ws
..Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
For Each c In _
..Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Offset(0, 1)
c.FormulaR1C1 = "=INDIRECT(""'"" &RC[-1]&""'!A7"")"
..Hyperlinks.Add Anchor:=c.Offset(0, -1), Address:="", _
SubAddress:="'" & c.Offset(0, -1) & "'!A7"
Next c
End With
End Sub

Have a look at the "Workbook_NewSheet" event as well

--


XL2003
Regards

William
(e-mail address removed)