+ Reply to Thread
Results 1 to 12 of 12

Combine data from multiple worksheets into a new worksheet

  1. #1
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Combine data from multiple worksheets into a new worksheet

    Hello all,

    Okay, so I have four worksheets that all contain the same header row in row 1, but different data in the data rows. I would like to combine all the data from each of the 4 worksheets into a new (created by code) worksheet named "WS Combine". The worksheet named "Result I want 01" simulates exactly what I want the "WS Combine" worksheet to look like. Can this be done?

    The header row, however, only needs to be brought over once (with all formatting intact; ie header pane frozen, yellow, centered & bold).

    The Worksheet named "Result I want 02" simulates the second thing I would like to do. This worksheet basically looks at "Result I want 01" and copies ONLY the rows that are RED and BOLD and pastes these rows (along with the header row). This worksheet could be named "Red Totals"

    A couple of nuances...

    1. The rows that are RED and BOLD in the four original worksheets are not always in the same position. That's because they don't currently populate that way so I wanted to make this as real as possible. Therefore, ideally, code that says "just copy all data from four worksheets" would not be sufficient.

    If it's not possible or too involved to have the worksheet named "Result I want 01" reorder the rows this way when copying them over, then having them in any order is fine.


    2. I need to keep the font formatting of ALL the rows intact as future code will not work without this formatting retained on the two new worksheets.

    3. It is possible that duplicate rows can be created (two worksheets have the same exact data) when combining these four worksheets into one. If this is the case, then either allow that to happen or simply delete the duplicate row, whichever is easier.

    Thanks a bunch!
    Attached Files Attached Files

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

    Re: Combine data from multiple worksheets into a new worksheet

    Programming seems unnecessary. Regardless of where the data ends up on each page, the "report" can be standardized and each row can find its data with simple INDEX/MATCH formulas.

    This is a little more work to setup the report pages the first time, but once you've done that, it's done. The rows will fill themselves out in realtime based on the current contents of the "can" pages.
    Last edited by JBeaucaire; 08-13-2009 at 12:06 PM. Reason: Removed book, use one further down.
    _________________
    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!)

  3. #3
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Combine data from multiple worksheets into a new worksheet

    Hi JB,

    This approach looks very interesting indeed. I'm trying to figure out how I can incorporate this into other workbooks with differently named worksheets. I'm getting this error:


    Please Login or Register  to view this content.


    on the line of code that says

    Please Login or Register  to view this content.

    I am probably wrong, but it appears that I'll have to "pre-fill" each excel object (worksheet) in VBA with the code "Option Explicit"? If this is the case, it won't work because all of my workbooks start with just one worksheet and continue to create new worksheets as they go along.

    I basically want to insert this code into existing code in other workbook macros, tell the macro which worksheets to look at, and what criteria to search for, which will always be different depending on which workbook I am using this code on at the time.

    Can this be done?

    Thanks much!

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

    Re: Combine data from multiple worksheets into a new worksheet

    I you look in your code above the "Next ws" line, you will probably find an IF statement without an accompanying END IF line to complete the section.


    Option Explicit
    is used to force you to properly declare and spell your variables. It's a wonderful aid to avoid problems later since it points out the errors specifically. I would always use Option Explicit, makes for safer coding.

  5. #5
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Combine data from multiple worksheets into a new worksheet

    JB,

    Wow, you got me stumped on this one. So, using your workbook, I had to remove the line "Private" before the sub, because I coudn't see the sub to "Play" it and I know that it becomes visible if it's a regular sub vs a Private one.

    I cleared all the contents from the "Result I want" worksheet, went to another worksheet in your "non-macro" workbook, "played" the macro and no luck, I got the same error.

    I need code to actually create the worksheet named "WS Combine" and have it look just like the "Result I want" worksheet. Correct me if I'm wrong, but isn't the code that you provided based on that worksheet already being there?

    If possible, this sub should run without any input from the user; ie...putting the words "option explicit" in all the active workbooks in VBA. If I put "option explicit" in "ThisWorkbook" in the Personal workbook, would that suffice?

    I want to put this sub in my "Personal" workbook so that I can activate anytime I need for whatever workbook I am working on at the time. I simply want to tell it what worksheets to pull the data from and what worksheet to paste all the data to.

    Thanks

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

    Re: Combine data from multiple worksheets into a new worksheet

    My apologies. The sheet I uploaded was named "Tuna-nonmacro" but I uploaded the wrong sheet. What you see in that macro was my initial thoughts on the topic when I realized a macro wasn't needed.

    All you need to do is mockup your report sheet and then use formulas to lookup the data in realtime. Look at the formulas on the RESULTS I WANT 1 and 2 and you'll see what I meant for you to look at.

    That macro wasn't close to being ready to use. I apologize again for uploading the wrong file.

    I've colored the cells to point out where to look.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Combine data from multiple worksheets into a new worksheet

    Hi JB,

    How do I translate this formula into a sub?

    How were the "Description" and "Totals" cells copied over from the four original worksheets to the "Result I want 01" and "Result I want 02 worksheets as they haven't been created yet?

    Keep in mind that the two new worksheets are not yet created at this point, so code would need to created to account for creating these two new worksheets.

    I would simply pretend that I did not include the "Result I want 01" and "Result I want 02" in my original upload.

    The reason I need this whole thing in "code/sub" version is because this will be in the middle of a long set of subs and this process needs to be automated along the way. I don't want to have to stop the macro from running, insert the formulas, and then continue the macro.

    You do have exactly the result I want though, it's just the process of getting there that is the issue.

    Thanks for your help on this

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

    Re: Combine data from multiple worksheets into a new worksheet

    I understand you indicate the # of entries on each page may change, which is fine. But is there ANY chance you can setup your "can" sheets to always have the "grand total" entry at the very bottom of the sheet? It seems odd in your example to find the total buried in the middle.

  9. #9
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Combine data from multiple worksheets into a new worksheet

    JB,

    Hmmm, well, I have code (below) that adds text totals of certain columns. Below is code posted by StephenR (Thanks again Stephen!). This is how I'm getting my text totals


    Please Login or Register  to view this content.

    So, the resulting totals on the "totals" worksheet is all blank plain text. I didn't really need everything in the columns totaled so, because I couldn't think of another way, I thought "hey, why not make ONLY the rows that I DO WANT in the newly created "Totals" worksheet LOOK different?".

    So...what I did was edit/replace "x" with "x" making the replacement that I chose, RED and BOLD. This way, I can make these rows "stand out".


    It would actually be perfect if there were a way to modify Stephen's code to get totals from multiple worksheets instead of one and put ALL of "text totals" onto just one "Totals" worksheet.

    Currently, I have to re-use the code above 4 times (1 for each worksheet and each in it's own sub) and just change the name of the "Totals" worksheet... ie "Damaged Cans" "Total Cans" "Opened Cans" "Closed Cans" so, basically, that's how I'm getting these 4 worksheets.

    I hope that answers your question

    Thanks for sticking with me on this JB, I really do appreciate it

  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: Combine data from multiple worksheets into a new worksheet

    I haven't done anything so far, so don't thank me yet.

    The code is interesting, but would be more meaningful attached to the data you run it against. Care to share the whole thing so I can see? Perhaps a workbook with the raw data and the existing macro. Then another "after" workbook so I can see what you want the COMPLETE macro result to be, not just this thread topic, but the whole thing.
    Last edited by JBeaucaire; 08-14-2009 at 06:04 AM.

  11. #11
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Combine data from multiple worksheets into a new worksheet

    I was just browsing over this thread, and I would agree with JB. Each of your 'tasks' seems to work in it's own right, but your overall project sounds like it is suffering fragmentation due to it being compiled from multiple Q&A on the forum.
    Also each section works well in it's own right, it is also having to 'cope' with the quirks that the previous section implemented, (such as you having to repeat code 4 times, when in reality a single function CALLED 4 times would probably do the job).
    If you post what you have, and what your 'end goal' is, then we / JB can focus on that, rather than each individual step.

    Good luck with it
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  12. #12
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Combine data from multiple worksheets into a new worksheet

    Phil and JB,

    Okay, the good news is that I managed to get all the text totals onto one worksheet. It took a lot of time and "newbie" patience, but I did it!

    I did this with a series of modifications to the "text totals" macro. I combined the value of 2 columns together in the same worksheet and put that value into another column, rather than going to a new worksheet.

    I can imagine that it's probably slow as molasses, but right now, I'm just happy to have it working the way that I want to.

    I have sooo many columns and things I did to this code that it will take me awhile to "dig through" it all to show you guys how I got to where I'm at using sample code, but..as soon as I am able to, I will have very equivelant working sample code so that you guys can see exactly what I'm doing here.

    You guys are going to laugh at how newbie this code is when I show you Seriously...Even being a newbie, I know it's newbie code and that's pretty bad

    Back to the fun stuff, I'm almost done with this particular project but now have the interesting task of exporting each rows value to a particular field in Word. I have attached a workbook to show you guys what I mean.

    So, now that I have all my text totals on one page, I need a way to do the following...

    1. Somehow, with code say

    Please Login or Register  to view this content.
    etc, etc for all 18 columns, using columns E & F as a reference to the Word Document.

    2. Somehow, through code from Excel or code from Word, both, or the next best way, (mail merge maybe?) put the totals from B into the Word document, using column C to index or match it.

    Questions I pondered myself while thinking about this

    >Can Excel/Excel VBA open up Word, if yes, a particular document?
    >Can Excel or Word exchange desired areas of text/totals through code?
    >If yes, can Excel tell Word to close and save the document under a new name?

    Picture the Word Document like a letter that you "TAB" through. Basically, I have a letter and just inserted 18 text form fields and then locked these form fields with the lock button on the Form toolbar. I suppose I should've put "Text Form Field" in the workbook instead of "Field" but at least now you know.

    If something other than text form fields should be there instead so that Excel and Word can speak correctly, no problem. I'd be happy to change them from Text Form Fields to anything...such as a FIELD for example. I'm not sure the best route for that.
    Whew, I'll check this one tomorrow, can't keep my eyes open anymore.

    Thanks guys!
    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)

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