+ Reply to Thread
Results 1 to 5 of 5

Get other sheet names into a column

  1. #1
    paul
    Guest

    RE: Get other sheet names into a column

    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!



    "Keyser" wrote:

    > 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.


  2. #2
    William
    Guest

    Re: Get other sheet names into a column

    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
    [email protected]


    "Keyser" <[email protected]> wrote in message
    news:[email protected]...
    >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.




  3. #3
    Keyser
    Guest

    Get other sheet names into a column

    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.

  4. #4
    paul
    Guest

    RE: Get other sheet names into a column

    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!



    "Keyser" wrote:

    > 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.


  5. #5
    William
    Guest

    Re: Get other sheet names into a column

    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
    [email protected]


    "Keyser" <[email protected]> wrote in message
    news:[email protected]...
    >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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1