Copying Data from one Execl to another Matching Headers.

  • Hello Everyone,


    I am new to VBA and hoping I can find help for my question here:


    I have a few excel which has 23 columns and another few with 7 columns with headers which I get daily with new data. I have another excel which is my master excel where I have all the data updated from the daily excel I receive. My master excel has only 12 headers and columns. The header names are not the same on the master excel and the daily excel that I receive but it is fixed headers on both the excels. On a daily basis I:


    1. Save all the new excel I received to the same folder as my master excel. Open my master excel, Open all the new excel I receive, manually match the header and copy data from the new Excel to the master excel, under correct headers, under the existing data on the master excel.
    2. Save my master Excel and close all the excel.
    3. Delete the new excels I received.
    4. Go one folder up and Open a new folder with today's date.
    5. Put a coy of my master excel with the updated data inside the new folder.


    I want to create a macro that can do all these with a click of a button and I want a macro that will open all my new excel one by one automatically and I want a macro that can match the headers on the master excel and the new excels I get daily and copy all the data from the daily excel go to my master excel find the next available cell on the master excel and paste the data under the correct header. Data in My master excel is in a table but the daily excel is not in a table. Also on my master excel, the first column is a reference number which is a running number, so every time a data is pasted the number filled with a reference. I want a macro that I can do all this with a click of a button as this is something I do everyday. However, the number of files I get daily is not fixed. Sometimes I get five sometimes seven, sometimes two. The headers on the files I receive is fixed and the header on my master excel is also fixed but they don;t have the same headers.


    Hoping for some help from you all please.


    Thank you

  • I can't promise a solution but it would be easier to help if you could attach a copy of your Master file and copies of one or two of your new files?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It looks like you have asked the same question at this link: https://www.mrexcel.com/forum/…her-matching-headers.html
    According to Forum rules, if you post the same question in a different Forum, you should post a link to that thread. This saves people from spending time coming up with a solution which may already have been solved in another Forum.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi All,


    To make it easy my column headers on my master Excel will be
    Reference, Name, ID, Date of Bith, Place of work, School, Education Level, Year Leaving School, Highest Education, Education expecting complete next, Martial Status, Ambition


    And the Heaaders of my daily excel that match my master excel will be
    Excel 1:



    Reference =


    Name = First Namr
    ID = Identificaiton
    Date of Bith = DOB
    Place of work = Work
    School = Edu
    Education Level = Qualification
    Year Leaving School = School Year
    Highest Education = Education Level
    Education expecting complete next = What you want to do
    Martial Status = Status
    Ambition = Vision


    Excel 2:


    Reference =
    Name = Full Name
    ID = Identificaiton
    Date of Bith = DOB
    Place of work = Work Plc
    Highest Education = Education Stats
    Ambition = What next


    PLease help me find a macro that can copy matching status and paste below existing data on my master file.


    Thank you

  • Are the headers in Excel 1 and Excel 2 the only possible headers that you will ever have in the source files? Perhaps you could upload a copy of your files to a free site such as http://www.box.com or http://www.dropbox.com. Once you do that, mark each for 'Sharing' and you will be given a link to each file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Are the source files named in such a way that can distinguish a source file 1 format from a source file 2 format?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • OK, so the file will have a 3 or a 2 at the end, not a 3 or a 1. I'll proceed on that basis.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Before we go any further, I would like you to try out what I have so far. Save [SIZE=13px]the attached 2 files [/SIZE]in the same folder. I will attach the Master file in anther post.

  • [SIZE=13px]Save the attached Master in the same folder before running the macro in Module1. Check the end result and see if it is what you want. Please let me know how it works out and we'll go from there. Please use these 3 files, as the files you posted had several typographical errors which caused a lot of problems trying to match headers.[/SIZE]

  • I ran the macro but I am getting an error. "Run-time error '1004': Sorry, we couldn't find LeadGen2.xlsx. Is it possible it was moved, renamed or deleted?" But all three files are in the same folder :(

  • Try shutting down Excel and re-starting it. Please use the revised macro below. It worked properly for me.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I tried, it says the same thing.


    I guess its picking up the excel name from the folder and something is not getting picked up right. The excel is there but its not recognize it and i can't figure why. I am so confused :(

  • Does the file have an "xlsx" extension? Did you save the Master file to the same folder as well? If it's not in the same folder, you'll get the error.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!