How do I merge 2 excel file with the same column headings into 1?

I have 2 files with identical column headings and different number of rows. Is there a way to merge these files automatically in a third file when you open it? Basically, every time a 3rd file is opened, the code deletes the information that was previously there and then populates it with the information from the original 2 files. The 2 original files are constantly updated; therefore the number of rows in each file is not consistent. However, the headers are always the same. And the files are matched by a header.

I hope this is somewhat clear.

My extent of dealing with macro is recording my steps when I am working through interface and then running it when necessary.

Please let me know if what I am requesting is possible and how it can be done. I appreciate all/any help.
Julia

Originally Posted by Yulichka

I have 2 files with identical column headings and different number of rows. Is there a way to merge these files automatically in a third file when you open it? Basically, every time a 3rd file is opened, the code deletes the information that was previously there and then populates it with the information from the original 2 files. The 2 original files are constantly updated; therefore the number of rows in each file is not consistent. However, the headers are always the same. And the files are matched by a header.

I hope this is somewhat clear.

My extent of dealing with macro is recording my steps when I am working through interface and then running it when necessary.

Please let me know if what I am requesting is possible and how it can be done. I appreciate all/any help.
Julia

Hi

Use the following code. You'll need to change the names of "Workbook1" & "Workbook2" to your names.

Please Login or Register  to view this content.

If there are either

1. More than one sheet in either of the two workbooks or
2. Blank rows in either of the sheets in the two workbooks,

then the macro above will need modifying.

HTH
2

This is working great! Thank you so much!

So now, I have the 3rd file, which I called Active Calendar. How do I automate this marco, so that every time I open this file it automatically deletes the information from rows below the header and runs the original macro to repopulate the file from workbooks 1 and 2.

Thank you so much for your help!

Originally Posted by Yulichka

This is working great! Thank you so much!

So now, I have the 3rd file, which I called Active Calendar. How do I automate this marco, so that every time I open this file it automatically deletes the information from rows below the header and runs the original macro to repopulate the file from workbooks 1 and 2.

Thank you so much for your help!

Hi,

Just post the code I gave you in the Workbook_Open event of your Active Calendar workbook.

Rgds

I would like to use this code but would like the macro to prompt me for the location of the workbook. I get tables every week with the same colun headings but the workbooks change names weekly. I'd like to save the workbooks to my file folder and then when I need to join them I'd like Excel to allow me to locate the files, click on them and run the macro.

Is that possible?

Hello thescats, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

I am so sorry. I will repost.