+ Reply to Thread
Results 1 to 17 of 17

Auto populate summary worksheet in text from multiple worksheets

  1. #1
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Question Auto populate summary worksheet in text from multiple worksheets

    Hi everyone,

    I have a spreadsheet tracking visiting professionals. On it I have a summary worksheet at the beginning and then the other worksheets are data for each professional (i.e. name, email, phone, etc.). Because there will be hundreds of worksheets, I want the summary sheet to automatically populate with the data from the detailed worksheets. I have read a lot of forums similar to this, but using numbers that need to be summed on one sheet from multiple sheets. I don't need any kind of formula, I just need a straight copy. I'm pretty sure that this will require writing VBA code and can't be done by recording a macro. This is new territory for me. Please help!

    Thanks!
    L

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Auto populate summary worksheet in text from multiple worksheets

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    Thanks for replying. I'm sorry for the delay getting back to you. I've attached to mock-ups of the files. The before file shows a summary page and three detail pages. The names of the learners have been hyperlinked on the summary page to go directly to their page. On the After file, I have added two additional worksheets and have put in formulas to automatically populate the Summary sheet as information is entered into these worksheets. What I need is to create multiple blank template worksheets with them automatically linked to populate the Summary worksheet on a larger scale so that I don't have to physically enter the formulas each time.

    I'd like to know how to do it in general because I have other sheets where I will need to recreate similar circumstances.

    Thanks so much for your help!

    L

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    Do you manually make a copy of the Template each time you need to add a new person and then manually enter their data? Will all 9 fields that need to be copied to the Summary sheet always have data?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    I copy the template and paste it into a new worksheet each time. I know there's a way to make a bunch of copies of the template at the same time, but I can't find the instructions where I did it before. The other part, where it autopopulates the Summary worksheet, I don't know how to program at all. Most of the fields will be filled, with the exception of Secondary Coach which may be blank sometimes. I still want it formulated to populate.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    The sheet names use only the last name. What happens if you have 2 or more people with the same last name? Do you want a button on the Summary sheet that you could click to add copies of the Template?

  7. #7
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    I just did this one as a mock-up. In the real one, the last name, first initial is used. Yeah a button would be great. It's a fairly new document so I anticipate the template changing a little bit. The main data being transferred to the summary sheet should be static.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    In order to make this work, the sheet names have to match the names in column B of the Summary sheet, that is, first name followed by a space and then last name. Would this be possible?

  9. #9
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    Sure, that would be fine. Would the sheets be ordered based on their name (i.e. alphabetically)? If so, it might be good to put last name first. I could always use two columns for the name (first and last).

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    No, they wouldn't be ordered alphabetically. I just realized that you can leave the sheet names as last name and first initial. If you decide to change to first name and last name that would eliminate the possibility of someone having the same last name and same first initial.
    Last edited by Mumps1; 03-26-2018 at 02:29 PM.

  11. #11
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    OK, so they would just be ordered based on when they were added (unless moved manually)?

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    That is correct. Try the attached file. You will see a button on the Summary sheet. Click it to copy the Template. Re-name the newly created sheet as you would normally. Enter the data in A1:F1 and A3:C3. Exit the last cell in which you entered data. The Summary sheet will have been automatically updated.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: Auto populate summary worksheet in text from multiple worksheets

    That's awesome. It worked perfectly. Do I need to just manually name each worksheet or did you do something there since you said it had to be the name in cell A1? If I change the template, will it change all future added templates? I'm very curious how you did it.

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    Actually, you can manually name the sheets any way you want. It won't affect the macros. The macro that copies the data to the Summary sheet can be accessed by right clicking the Template tab name and clicking 'View Code'. This macro is a Worksheet_Change event which is triggered by a change in any cell in the input range. So when you enter the name in A1, it will be automatically copied to the Summary sheet. The rest of the inputted data is copied the same way. The macro that copies the template is in Module1 that can be accessed through the VBA Editor (Hold down the ALT key and press the F11 key). I hope this helps.

  15. #15
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27
    Thatís incredibly helpful. I really canít thank you enough.

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    You are very welcome.

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,822

    Re: Auto populate summary worksheet in text from multiple worksheets

    Please use the attached version of the file instead of the previous one. I realized that if you left any of the input cells blank, the next time you entered data for a new person, the data would not be copied to the correct rows. The attached version fixes that problem.
    Attached Files Attached Files

+ 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. [SOLVED] Search multiple columns from Multiple Worksheets and copy rows into a Summary Worksheet
    By kljohn01 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-07-2017, 03:46 PM
  2. Auto-populate a worksheet from multiple worksheets, based on unspecified dates.
    By colinirving99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2014, 03:12 PM
  3. Replies: 1
    Last Post: 08-20-2013, 06:50 AM
  4. [SOLVED] Same Cell From Multiple Worksheets into Summary Worksheet
    By DonMegel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-11-2013, 03:31 PM
  5. auto populate within multiple worksheets
    By r3b3ckah in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-02-2009, 12:52 AM
  6. [SOLVED] I want to conditionally auto-populate a summary worksheet
    By trigger in forum Excel General
    Replies: 0
    Last Post: 01-13-2006, 11:35 AM
  7. [SOLVED] Summary worksheet referencing multiple worksheets
    By Jon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2005, 01:21 AM

Tags for this Thread

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