Merge Excel Sheets Using VBA

Question

I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with headings in the first row and Upto "AN" Columns. This No of rows(5000) doesn't change for a whole year. Now i have 5 XL files(Say A,B,C,D,E) and the data from these files has to be appended to this OG file just starting from 5001st row every time. All these 5 files has different no of columns but identical to that of OG File. I have to pull data from these files and place them in OG File. From File A : Column A,B,C,D,E,F,G&H goes to Column F,G,T,U,V,W,X&Y Of OG.xls File. Likewise the other files data has to be extracted according to the corresponding column with OG.xls

The second file data has to be appended right below the next row where the File A ends.(Say after filling the data from File A now the OG.xls has 5110 rows, the File B data has to filled from 5111 st row of OG.xls. The same follows for the other files too. The data of these 5 files has to be filled row after row but should match the columns to that of OG.xls

Each time the same operation is repeated by filling the data from 5001st row of OG.xls. For convenience we can have all these files in a same folder.

How can we do this.

Please help me in this!!! Also let me know for any clarifications.

Answer 1

If you need a more presice answer, you would need to try something first and then ask for help in area you have got stuck. My suggestion is you begin by; 1. Start writing a VBA script in OG.XLS, as a first step try to access the file A.xls and reading the columns and pasting them (they can initially be at any location in any order). 2. Once you are able to do this, next step is to see if you put the data in right column (say 5000 in your example) by setting up right kind of variables and using them and incrementing them. 3. Your next step should be to to read the column headings in A.XLS and finding them OG.XLS and identifying them. Initially you can begin by doing a simple string comparision, later you can refine this to do a VLOOKUP. 4. During this process, if you encounter any specific problem, raise it so that you will get a better answer.

Few from the community would go to the extent of writing the entire code for you.

Answer 2

I come across this problem when one of my client came to me looking for solution to merge their stock lists that are saved in more than 200 separate files. If you found yourself to be in the same position like my client; don’t worry, I wrote a simple program that do the work. :) Check the link below:

JMC Excel - Join, Merge, Combine multiple Excel sheets or Excel workbooks

Regards, JeeShen Lee www.jeeshenlee.wordpress.com