Combining multiple workbooks into one workbook

I would like to combine multiple workbooks into one workbook (I even got myself KUTOOLS) but I just can't figure out how to do it!

To explain what I need Excel to do, I made up an easy example that explains my situation! The data in the Excel workbooks are written sentences, all workbooks have the same column headings for example letter, name, aadress, telephone.

Different people have filled the Excel table in with their name, aadress, telephone according to their first name letter. Lets say there are 20 of these workbooks. Each person has written their data in different rows, so for example Adam used row 1 for the letter A, Bethany used row 2 (left ohter rows empty) etc. Now I would like to combine all these 20 workbooks into one.

It would look something like this:

Adam sent me this:

Letter Name Address Telephone
A Adam House str 63366366
B

Bethany sent me this:

Letter Name Address Telephone
A
B Bethany Window str 564767576



I need to combine them into this:

Letter Name Address Telephone
A Adam House str 63366366
B Bethany Window str 564767576


I would greatly appreciate any help as I work with situations like these and I just can't figure it out!

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

I can think of a macro to do this, but you have this in the formulas & functions forum.

Check out this clip here.

https://www.youtube.com/watch?v=6XsvI9i2z7s

Also, the names do not have to be in their own row, just sort the column.

Hi Dave,

I have a question regarding this.

Would this work if my files are in .csv format? I noticed that in .csv format the worksheet names are the filenames. I believe your code picks up the data in worksheets called "Sheet 1" from multiple workbooks.

Is there a way to change it so that it picks up whatever is in the 1st sheet of the files? - assuming the worksheet names are different due to .csv format.

*Editing to include this real quick..

It looks to be this line.

With Worksheets("Sheet1")

What would be the new line/lines to add/change if I wanted it to pick what is the 1st sheet(only sheet) in the files?

Much appreciated!!!

Hi zed369,
Although you have hijacked somebody else's thread,
You don't have to use the sheet name, you can use Sheets(1) in place of Worksheets("Sheet1")