+ Reply to Thread
Results 1 to 8 of 8

Table of Contents/List

  1. #1
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Table of Contents/List

    Hello Excel Gurus,

    I require a macro in the form of a button called "update record" that will update a table/list every time a new worksheet is created. In essence this list is like a table of contents.

    The headings in the list will look like:

    Cheque# Name Date Amount

    The cheque number will be populated every time a new worksheet is created with the name of the cheque number. I will be using the move-or-copy function in excel to copy a worksheet and will rename the worksheet name with a new "Cheque Number". New worksheets are added daily so the workbook keeps on growing.

    The name, date and amounts are in the same row/column positions in every worksheet but will need to be referenced in the list and also be updated with the details from the newly created worksheet. There are also about 50 or so worksheets that are already in the workbook so I'm wondering if the details of these worksheets can also be linked to the table.

    Appreciate any help.

    Thanks

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Table of Contents/List

    Clearly a sample of the file is needed to see how is the data layout: Remove personal data and reduce the number of sheets
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: Table of Contents/List

    Please see excel file attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: Table of Contents/List

    Also hoping if a hyperlink could be added to the code, so that when the user clicks on the cheque# it goes to the relevant tab.

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Table of Contents/List

    So for all sheets (except the one named "List") you want to pick up cells C1, C2 and I45 and put these vales with the sheet's name in sheet "List"
    If the sheet 's name already exist it erases old values.
    Is it ?

  6. #6
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: Table of Contents/List

    That is correct I would like all sheets except the one named "List" which will pick up cells C1, C2 and I46 and put these values in the worksheet named "List".

    I would like to have a button called "update record" that will add onto the list every time a new worksheet/tab is created. Or better would be to have a macro that searches for all the worksheets in the workbook and lists the names along with the values (C1,C2,I46) in each worksheet. That way if a worksheet gets deleted the list will reflect what is in the workbook.

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Table of Contents/List

    Try next code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: Table of Contents/List

    That's awesome! Thank you very much it works

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-04-2014, 09:51 AM
  2. [SOLVED] Automatically revise Table of Contents list when worksheet is hidden
    By rbaughman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2013, 11:30 AM
  3. Copy list contents down, then clear contents
    By hektisk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2011, 04:34 PM
  4. Table of contents for contents of 1 sheet
    By dhbyrne in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-15-2007, 06:39 PM
  5. How can I list worksheet tabs as a table of contents?
    By aellorac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2006, 07:15 PM

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