Closed Thread
Results 1 to 15 of 15

Simple Macro to Create Summary Sheet

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Simple Macro to Create Summary Sheet

    I do not have a file to attach as an example at this time so I will do my best to explain. In Excel 2003, I have a workbook which will contain an unknown number of sheets. I need the macro to go through each sheet and pull data to a summary sheet. There could be an unknown number of sheets so it will need to look to see how many to end.

    Summary sheet will always be the first sheet.
    The column headings on each sheet match the summary sheet except for the Totals column of my summary sheet (this may not matter)
    Column A ="Project" Column B="Name" Column C="Number" Column D=AccountNumber", Column E="Totals" on my Summary Sheet and its contents are most of the time in cell F24

    Cell A2 on each sheet will contain a value that needs pulled to Cell A2 on the Summary Sheet
    Cell B2 on each sheet will contain a value that needs pulled to Cell B2 on the Summary Sheet
    Cell C2 on each sheet will contain a value that needs pulled to Cell C2 on the Summary Sheet
    Cell D2 on each sheet will contain a value that needs pulled to Cell D2 on the Summary Sheet
    Cell F24 will be my Totals column on the worksheets and will need pulled to Cell E2 on the Summary Sheet

    Please help.

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Simple Macro to Create Summary Sheet

    Do you mean "Cell A2 on each sheet will contain a value that needs pulled to Cell A2 on the Summary Sheet" means you want a SUM of all the other sheets cell A2
    Click on star (*) below if this helps

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Simple Macro to Create Summary Sheet

    On Sheet1, in cell A2:

    =SUM(Sheet2:Sheet10!A2)

    Copy that cell across thru D2.

    In E2:

    =SUM(Sheet2:Sheet10!F24)

    ---------- Post added at 12:18 PM ---------- Previous post was at 12:13 PM ----------

    A macro that does the same thing for you:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    Thank you. I will not be able to try till Monday but it seems like it will work. I will post back. Thanks again.

  5. #5
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    I was unable to make it work so I have attached a sample file. To reiterate,

    The summary sheet is always Sheet1
    The data I need to pull from each worksheet to the summary sheet will always be in A3:E3 and cell K12.
    The summary sheet should pull the data to rows in the summary sheet, 1 row per detail sheet beginning in cells A2:F2.
    The macro should look through the sheets to end when no more sheets exist stop pulling the data.

    Thanks.Template for Create Summary Code.xls

  6. #6
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    I wanted to mention my spreadsheet seems to be referencing other worksheets. So your code may not work in my workbook. I'm not sure why it is doing this. I have been experimenting quite a bit. I just wanted you to have a sense of where the data is coming from and going to.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Simple Macro to Create Summary Sheet

    No, I finally looked at your sample and it appears your question is horribly mispresented.

    You don't want the values from A2 on every sheet SUMMED into a single cell on the Summary. You want cell A3 from every sheet to be listed on separate rows.

    Here's a basic code to just copy all the sheet cells into sequential rows when you run it on-demand, this macro would go into a standard code module:

    Please Login or Register  to view this content.

    Now, if you want to get really fancy, right-click on the SUMMARY tab and select VIEW CODE to open that [U]sheet[/U module and paste this other macro into that module to automate this whole process:
    Please Login or Register  to view this content.

    What will happen now is that anytime you go to the other sheets and make changes, then bring the Summary sheet back onscreen, the macro will trigger itself and reassess all the content.

  8. #8
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    Thank you. Sorry about not expressing this correctly. I am very bad at this and trying to learn. I will try your code this morning and post back. Thank you.

  9. #9
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    Wow! This works great. I am so happy. I'm wondering if you are able to help me with my next problem. It is a little more complex and deals with the same workbook and an additional worksheet which I have attached. If not, I can post in the forum, it is quite complex.

    My process begins with the "Monthly" worksheet. I receive the worksheet and need to populate my template workbook with the data in the following manner:
    Assumptions:
    1. The "Create a Summary Sheet" template workbook contains the Summary worksheet and 1 detail worksheet only.

    Here's what needs to happen:
    1. The information contained in the "Monthly" worksheet needs to populate my template workbook in the following manner:
    - The detail template sheets need copied and populated with select data located in each row
    of the "Monthly" sheet.
    2. The worksheets need named according to the data listed in (Column B) of the "Monthly" worksheet.

    DETAILS:
    The data in the "Monthly" worksheet(Columns B:F) needs populated in my template workbook into (Columns A:E, Row3) creating seperate detail worksheets for each row listed on the "Monthly" worksheet.
    After creating the worksheets they need named either from the data located in the detail sheet (Column A, Row 3) OR by taking the data in the "Monthly" (Column B) and naming the worksheets from there.

    THE CATCH
    The "Monthly" worksheet data is seperated into template workbooks based on the value in (Column E). Normally I need to sort by (Column E) first, then create template workbooks, then populate those workbooks. (which I can still do) Someone told me I should try and write a macro to create the template workbooks first then populate them, however, it all seemed overwhelming so I gave up on that part. I figued if I could get it to autopopulate I could delete text in my "monthly" worksheet and run the macro on the needed data only however many times I had to do it.
    Attached Files Attached Files

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Simple Macro to Create Summary Sheet

    The title of this Thread is "Simple Macro to Create a Summary Sheet". As per forum rules, you did a great job titling this thread accurately.

    Now, as per forum rules, mark this thread as Resolved (Thread Tools menu above) and post a new thread with another accurate title, "Create Sheets From A Template"

    Pop me a link to the new thread if you wish. See you over there.

  11. #11
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet

    Thanks I am very new to this.

  12. #12
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Simple Macro to Create Summary Sheet


  13. #13
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Simple Macro to Create Summary Sheet

    I am looking to create a summary sheet for about 15 subsheets. The summary sheet should automatically pull up each entry from each subsheet for the task type, date, month and the sector (15 sectors i am working on). the sector subsheets will be updated by various individuals and hence the number of entries is not predetermined.

    I would also like to know once this works, how to autosort the summary sheet so the data is visually appealing.

    Many thanks for any advice, previous help to me indicated use of "DIM" function, I am new to macros so definition for the commands would really help.

    Thanks once again!

  14. #14
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Simple Macro to Create Summary Sheet

    forgot to mention i am using Offcie 2010

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Simple Macro to Create Summary Sheet

    Loblaws,

    When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)

    Your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed 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