+ Reply to Thread
Results 1 to 14 of 14

Merging / Consolidating Worksheets

  1. #1
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Merging / Consolidating Worksheets

    Hi Everyone,

    I am working on a project for fun but I am struggling to consolidate a few worksheets (not all worksheets) with VBA into a "Master" worksheet.

    The idea is to copy all data over from tabs with "ABC" in the worksheet name, i.e. copy from "ABC - 1", "ABC - 2", "ABC - 3", but not "XYZ - 1" or anything without "ABC".

    All the ABC worksheets have the same header (Name, Price, etc.), so I would like to retain this only once when merging the data.

    If anyone can figure out the VBA code, that would be really helpful! I have tried multiple times and browsed sites, but have not been successful.

    I have attached an example of what the "Master" sheet would look like, and the corresponding tabs.

    Thank you!
    Attached Files Attached Files
    Last edited by jos283; 04-30-2020 at 09:48 PM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    Something like this.
    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merging / Consolidating Worksheets

    Hey Jos,

    I'm always amazed at how smooth jindon is at VBA. Here is my long winded attempt to do what you wanted. Perhaps it is more readable?
    Please Login or Register  to view this content.
    See the attached workbook below:
    Copy ABC Sheets to Master.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Merging / Consolidating Worksheets

    An alternative solution is to use Power Query. Load all sheets to PQ and append each sheet to the other.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Re: Merging / Consolidating Worksheets

    Thank you so much Jindon - this worked!

    I have a follow question if you have time to spare.

    I would also like to carry out the following:

    - Rename the values in the name column in the "Master" worksheet based on the "Name Key" tab (i.e. if name is Michael, change to Mike). Some sort of lookup?
    - Rename the name for Juliana to either "Jules (1)" or "Jules (2)", based on the value in the date column. If date is before 10/1/2020, then change to Jules (1). If date is on or after 10/1/2020, then change to Jules (2). Just for this name though, no-one else.

    If you know how to perform this, that would be really helpful! I have attached a second file with this extra info called Example 2.

    The highlighted tab - "Intended Output - Master" is what the "Master" tab should look like, and I've also included a refresh button.

    Thanks again Jindon!

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    Do you mean like this?

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Re: Merging / Consolidating Worksheets

    Almost! I'd like the (1) or (2) based on the date just for the name Juliana, so Jules (1) or Jules (2) - but the others will just appear as Bob, Mike, etc. (not Bob (1), Mike (1)).

    Thanks again.
    Last edited by jos283; 04-30-2020 at 09:22 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    OK,

    I'm out at the moment, so I will fix it later in about 3,4 hours.

  9. #9
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Re: Merging / Consolidating Worksheets

    I can wait, that's fine!

    I've attached another file ("Attempt 3") if you could look at this one instead. I've actually been playing around to see what happens if I switch the column order as well, but for some reason, it keeps changing the column header to "Price", taking it from the first column I believe instead of leaving it at "Name" when I type that in. Maybe you could assist with that after helping with changing just the name Juliana to Jules (1) or (2)?

    The latest code from you with some minor tweaks of mine is the following (you can also find it in Developer in the "Attempt 3" file):

    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    Try this
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Re: Merging / Consolidating Worksheets

    I'd say it works 95-99% of the way, but for some reason, it's still changing the column header from "Name" in Column F to "Price" from Column A? So there's 2 price columns.

    Also, noticed that when I replace a name in say "ABC - 1" (i.e. Michael to Wesley, or a name that isn't in Name Key), the code takes values from Column A instead of leaving the name as it is from "ABC - 1"? Is there a way to not edit the name if the name does not exist in the Name Key, rather than replacing it with a price?
    Last edited by jos283; 05-01-2020 at 01:31 AM.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    OOps, of course...
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-21-2017
    Location
    NY
    MS-Off Ver
    16.0
    Posts
    25

    Re: Merging / Consolidating Worksheets

    Amazing. I believe this works perfectly now! Thank you so much

    This was a great learning experience.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Merging / Consolidating Worksheets

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. merging and consolidating spreadsheets into one
    By gotchamed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-16-2019, 10:50 AM
  2. Replies: 9
    Last Post: 04-09-2018, 06:59 PM
  3. Replies: 1
    Last Post: 11-02-2017, 09:20 PM
  4. Consolidating- Merging multiple worksheets into 1
    By chillz in forum Excel General
    Replies: 2
    Last Post: 05-12-2017, 11:35 AM
  5. Merging or Consolidating Contacts
    By jdr7785 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-23-2014, 10:53 AM
  6. merging and consolidating two data sets
    By ianmacneil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-18-2011, 09:45 PM
  7. Data Merging/Consolidating between Multiple Spreadsheets
    By trand007 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-10-2010, 02:02 PM

Tags for this Thread

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