+ Reply to Thread
Results 1 to 11 of 11

Combine multiple sheets into master sheet, and include sheer name

  1. #1
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Combine multiple sheets into master sheet, and include sheer name

    I have a WB with multiple sheets with identical layouts, just the naame and data change.

    I need to combine all sheets (about 30 or so) into 1 master sheet, but I also need to include the sheet name that the data came from - in a new column (A).

    So...
    copy all of sheet2 (A:N) to sheet1 B:O) and then enter "Sheet2" in column A in the corresponding rows
    move to the next sheet, rinse and repeat for all sheets

    I can dummy-up a sample WB if needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Combine multiple sheets into master sheet, and include sheer name

    Hello Fdibbins,

    Something like this?

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Combine multiple sheets into master sheet, and include sheer name

    Hi FDibbins,

    Let me know how this goes:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Combine multiple sheets into master sheet, and include sheer name

    Thanks guys

    I made a rookie mistake by not supplying more details

    1. I have a 2 sheets that need tpo be excluded from the combination (I can get around this by saving as a dummy file and deleting those 2, so no real big deal there)

    2. each sheet has 1:2 as headings, I would only need that copied over once

    Also, I should have asked - to these create a new WS or do I need to add my own
    (edit: I see I need to add my own)

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Combine multiple sheets into master sheet, and include sheer name

    Hi Ford,

    1. I have a 2 sheets that need tpo be excluded from the combination (I can get around this by saving as a dummy file and deleting those 2, so no real big deal there)
    If you let me know the sheet names I will exclude them in my code.

    2. each sheet has 1:2 as headings, I would only need that copied over once
    Copy the headings to the 'wrkConsSheet' consolidation sheet and set the 'conMyStartRow' variable to 3

    Regards,

    Robert

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Combine multiple sheets into master sheet, and include sheer name

    The sheets are Data and Summary

  7. #7
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Combine multiple sheets into master sheet, and include sheer name

    OK - change this line of my code...

    Please Login or Register  to view this content.
    ...to this:

    Please Login or Register  to view this content.
    Just bear in mind that if these sheet names change, the code will also have to change. If this is percieved to be an issue, we can use the code name of the sheet.

    Regards,

    Robert

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Combine multiple sheets into master sheet, and include sheer name

    Im going to have to put a WB together for you, I followed your directions...
    Please Login or Register  to view this content.
    But it is still pulling in Data and Summary

  9. #9
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Combine multiple sheets into master sheet, and include sheer name

    Ah sorry about that (bit hard without replicating each sheet tab). See if using this line...

    Please Login or Register  to view this content.
    ...instead of this one does the job:

    Please Login or Register  to view this content.
    Regards,

    Robert

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Combine multiple sheets into master sheet, and include sheer name

    I dummied up something
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Combine multiple sheets into master sheet, and include sheer name

    Might need some formatting, but have a look at the attached.

    Also, each time you run the macro the data is appended. Is this correct or should there be an option to clear the existing consolidated data first?

    Robert
    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. Combine multiple sheets into one master doucument.
    By postrander in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2014, 07:54 PM
  2. Combine folder sheets to one master sheet
    By rchure in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2014, 01:11 AM
  3. Combine folder sheets to one master sheet
    By rchure in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-24-2014, 10:48 AM
  4. [SOLVED] Macro to combine specific sheets into master sheet
    By JakubPrchal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2012, 08:34 AM
  5. Anyway to combine multiple sheets into master?
    By rbzurich in forum Excel General
    Replies: 1
    Last Post: 06-12-2007, 11:13 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