+ Reply to Thread
Results 1 to 4 of 4

Merge data from multiple worksheets

  1. #1
    Registered User
    Join Date
    12-09-2020
    Location
    Riyadh
    MS-Off Ver
    2013
    Posts
    2

    Merge data from multiple worksheets

    Hi all,

    I am new to VBA and forums in general.

    I am trying to compile multiple worksheets from different workbooks. The worksheet name is identical in all workbooks. Each worksheet is supposed to have the same column headers. I am looking for a way to do following:
    1) Test if the column headers in all worksheets are identical (and notify me if there are errors and in which sheet)
    2) In case the column headers in all worksheets are identical, compile the contents of the worksheets in a new workbook (using on the column headers).

    Can this be done in VBA or other ways? especially the first part?
    Last edited by HCube79; 12-09-2020 at 01:59 PM.

  2. #2
    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: Merge data from multiple worksheets

    Easily accomplished with Power Query.

    First: Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    Second: https://www.youtube.com/watch?v=HcfKOusOJhA This is a You Tube on how to combine Tables or worksheets
    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

  3. #3
    Registered User
    Join Date
    12-09-2020
    Location
    Riyadh
    MS-Off Ver
    2013
    Posts
    2

    Re: Merge data from multiple worksheets

    Thank you for the prompt reply.

    The problem with power query is that I have to manually go to each worksheet and make sure the column headers are identical. For example, i have column "Date" in worksheet #1. The header for the same column in worksheet#2 is written "Datee". This will create two separate column if I use power query.

    Is there a way to identify such error before starting the merge? probably VBA?

    Thanks again.

  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: Merge data from multiple worksheets

    Simple answer is yes. You will need to make sure that the columns are the same. You can do this with VBA by doing a If Then scenario. There is no generic code for this. You will need to supply some sample worksheets to allow us to build any type of VBA and identifying what each column name should be.

+ 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. [SOLVED] Marge data inside Master workbook getting data multiples files based specfic sheets
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-22-2021, 07:14 AM
  2. [SOLVED] how to marge multiple workbook into one sheet (not working)
    By emmr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2019, 07:23 AM
  3. [SOLVED] Macro to pull columns of data from multiple worksheets into summary worksheets
    By crandell84 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2018, 05:44 PM
  4. [SOLVED] Transform column to row. And marge some data.
    By dosi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2017, 12:55 AM
  5. Replies: 2
    Last Post: 10-01-2014, 04:18 PM
  6. [SOLVED] Marge multiple row value in one cel.
    By ganeshkumar in forum Excel General
    Replies: 1
    Last Post: 03-22-2014, 08:51 AM
  7. Copy/Paste Range of Data from Multiple Workbooks/Worksheets to Master Workbook/Worksheets
    By NumberCruncher311 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 08:21 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