+ Reply to Thread
Results 1 to 14 of 14

Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbook

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbook

    Good afternoon.

    This is such a great site with a wealth of information.

    I always considered myself a fairly good excel user, until stopping by here Now I realize I haven't even scrapped the tip of the iceberg.

    Here's what I'm up against:

    I have multiple sheets on an order form type of file, that I want to pull only the selected quantities and place them on a "Summary" page of sorts. This data is inputted with a number 1-infinity.

    Seemed pretty easy when I took the task on, but I just am drawing a blank now from a coding perspective.

    I've attached the worksheet so you can see. I'd like to ignore "PO info" and "Order Total" tabs.

    Thanks so much for any input here.

    Regards,
    David
    Attached Files Attached Files

  2. #2
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    David, it's not exactly clear what you are trying to do. Could you attach an example of the "after" version of your code/formulas. That is, be specific about what you want to summarize where.

    Greg
    Just a guy trying to make work stuff easier.

  3. #3
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Greg,

    sorry for the confusion!

    Here's an "After" version of the summary page. The info is dependent on what the user selects on the other tabs. A compilation of sorts if you will.

    PLEASE NOTE: I just copied the info over, it isn't due to code on the summary page. I am hoping there is a way to automate this, by when they enter a quantity for order.
    Attached Files Attached Files

  4. #4
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    So, I'm envisioning a user entering the quantities of each product/service on each of the sheets, then, when complete, maybe clicking a "summarize" button on the Summary sheet, and the code will look at each sheet to determine where a quantity was entered, then pull the info for that row back to the summary sheet?

  5. #5
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    That is a wonderful summary to what it could look like.

    Is this even doable in Excel?

  6. #6
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Maybe something like this? Note, I did not spend a lot of time on formatting, just recorded a macro to do the cell borders. It's pretty clear where I inserted the recorded stuff, so you should be able to record any formatting you want and paste it in.

    Greg
    Attached Files Attached Files

  7. #7
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Ok, I seem to have messed up the attachment, as when I download it I cannot get the macro to run eventhough it is in the workbook. Anyone have any ideas? Thanks.

    Greg

  8. #8
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Greg,

    This is a great start.

    One quick question: how do I get the data to clear with each "Summary" button that is clicked? Right now, it just copies over the data, but if the new data is less, it leaves the old datar/rows below it present.

    I'll keep working on this to see if I can get it debugged...

  9. #9
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Well, I did not do anything about that because that is a question you must decide. My thought would be to have a "Save" button, then export the summary, then clear it out. If you don't want to save it, you could simply delete the data rows. Either way, it's a simple matter to delete rows. You could record a macro deleting the rows, put it in a sub called 'Clear', then the first line of sub 'Summary' could be 'Call Clear'. Anyhow, let us know if you need more help.

    Greg

  10. #10
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Greg,

    The formula's are working great. Thank you kindly for your help.

    I got the CLEAR function up and running as well... while leaving the header. Wonderful

    The only thing I have left is how to get the cell formatting to copy for each line that is summarized. Right now it only goes from A3:J8 i believe... how do I get the "box outlines" to mirror the data summarized.. if it's 7 lines, or 15 lines, or 200 lines?

    Thanks!

  11. #11
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Oh and here's the worksheet BTW.
    Attached Files Attached Files

  12. #12
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    Try this one...

    *EDIT* One more thing: There is a line in there 'Debug.Print ItemCt'. You can either comment it out, or delete it. It does not really hurt anything, but it's only for displaying the value of the variable in the immediate window for testing. Also, I'm glad you figured out the clear part yourself. If you wanted to, you could put it in the same module as the other, just to make editing a bit more friendly.
    Attached Files Attached Files
    Last edited by gsnidow; 01-14-2014 at 09:43 PM. Reason: Had one more thought, had not be responded to yet

  13. #13
    Registered User
    Join Date
    01-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    That is perfect! Thank you so much.

    So if I understand correctly, you can set the borders to simply be G3:G.... continuously? I am not sure I fully understand this code yet, but it works perfectly.

    Thank you again Greg.

  14. #14
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Creating a Summary Page of Selected Data Lines from multiple worksheets in same workbo

    No problem, I'm glad it works for you. When it comes to formatting, I don't much have a need to commit it to memory, so I always just record and alter. Try this: start recording a macro, then highlight a range, and format the borders using the border button, then stop recording. The macro will be stored in a new module probably, but when you look at it, you will notice it bears a striking resemblance to the formatting code in the summary sub. The only thing you have to change is to make the range variables. Anyhow, good luck to you.

    Greg

+ 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. Creating a summary page of data found on several worksheets
    By Therese in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2013, 01:31 PM
  2. Pulling selected data from multiple worksheets into sales summary by vendor
    By ejoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2013, 02:34 PM
  3. [SOLVED] Creating a summary page from multiple worksheets
    By abbysawisch in forum Excel General
    Replies: 7
    Last Post: 06-19-2012, 09:45 AM
  4. creating summary table from selected rows of multiple worksheets
    By jrtaylor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2009, 11:54 AM
  5. Replies: 1
    Last Post: 07-17-2006, 06:55 AM

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