+ Reply to Thread
Results 1 to 11 of 11

A macro to combine multiple worksheets

  1. #1
    Registered User
    Join Date
    03-03-2010
    Location
    Raton, NM
    MS-Off Ver
    Excel 2003
    Posts
    8

    A macro to combine multiple worksheets

    I have been trying to find a solution to my problem for a few months now and I have, thus far, been unsuccessful at finding a sufficient solution.

    Just to give a bit of background info: I am a biologist working with formulas to distribute hunting pressure across the State of New Mexico. I have a worksheet for each Game Management Unit in the State and each Unit contains its own hunt objective. We have properties that fall in multiple units (all under a unique identifier), and these are causing the major problems.

    I have about 40 worksheets that have the same header information in rows 1 through 7, but a varying number of rows below that information (from a few to 200). Row 8 is the first row with differing information on each worksheet.

    I have been trying to come up with a way to combine all worksheets into 1 worksheet and have Excel combine all instances with a unique identifier into 1 summed row (i.e. if a property has 1,000 ac and 1 tag in Unit A, and 4,000 ac and 4 tags in Unit B, I want the final to have 5,000 ac and 5 tags). Not all properties have more than 1 entry, but some have 3.

    Ultimately I would like to run a macro each time we need the single worksheet with all properties listed on it, but haven't been able to figure things out (I am not that good with macros).

    I have gone through and done things manually and it takes about 5 hours and I make mistakes. I would like something quicker and more foolproof than I am.

    Any help is greatly appreciated.

    Ryan
    Last edited by alloutdoors681; 05-05-2011 at 10:05 AM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Help needed! A macro to combine multiple worksheets

    To make a summary in a summarysheet:

    Please Login or Register  to view this content.
    The combining of data is very hard to make any suggestion about, because we have to be familiar with the properties of your date (please post an example workbook).



  3. #3
    Registered User
    Join Date
    03-03-2010
    Location
    Raton, NM
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Help needed! A macro to combine multiple worksheets

    Here is an example of the spreadsheets for 3 units. The T.O. Ranch has portions of the property that falls in 3 different units, all under the ID number 19760.

    Once all of the worksheets are concatenated into the summary page is there a good way to have Excel go through the data and add up all instances of a given ID number?

    Thanks for the help,

    Ryan
    Attached Files Attached Files

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help needed! A macro to combine multiple worksheets

    Hi Ryan

    Can we assume all worksheets are in the same workbook?


    What formulas do you need to come across in the combined worksheet?
    • All
    • None
    • Some
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    03-03-2010
    Location
    Raton, NM
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: A macro to combine multiple worksheets

    All worksheets will live in the same workbook.

    I don't necessarily need the formulas to come across, but I do need the values from each worksheet to carry over.

    My plan is to run this twice a year rather than maintaining changes to existing entries or adding new entries to the summary page.

    Ryan

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: A macro to combine multiple worksheets

    What about the formulas in your header...should they be recalculated?

  7. #7
    Registered User
    Join Date
    03-03-2010
    Location
    Raton, NM
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: A macro to combine multiple worksheets

    I don't need the information in the first 7 rows of each worksheet for the summary, just the values from row 8 and below for each worksheet.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: A macro to combine multiple worksheets

    I'll look at it.

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

    Re: A macro to combine multiple worksheets

    I had an existing macro here that does the merging already:I tweaked that to ignore your first 6 rows. Then added a MergeRows function after that to merge it all down. Try it out, i installed the finished macros in your sample wb:
    Attached Files Attached Files
    _________________
    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!)

  10. #10
    Registered User
    Join Date
    03-03-2010
    Location
    Raton, NM
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: A macro to combine multiple worksheets

    That looks like it should work just the way I need it to.

    Thank you very much,

    Ryan

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

    Re: A macro to combine multiple worksheets

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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