Copy Multiple Columns From Multiple Excel Files & Paste Into 1 Workbook

[pnssassin]

Dave i dont think you read my question or the code i gave you. Can you please re-read it and give me a complete answer? you answers are very vague.

[Dave Hawley]

You gave me code? Your questions are very vague.

[pnssassin]

I have five files:

a.xls
b.xls
c.xls
d.xls
Combined.xls

Files a, b, c, and d all have a set of information in them. I want to copy the data from each file a, b, c, d from cell A10 to Nx and paste it into combined.xls one after another.

The combined.xls would look like:

data from a.xls
data from a.xls
data from a.xls
data from a.xls
data from b.xls
data from b.xls
data from b.xls
data from c.xls
data from c.xls
data from c.xls
data from c.xls
data from d.xls
data from d.xls

As you can see not all of them have the same amount of data. That’s why I used the Nx. The range starts from A10 but ends at N and the FRIST BLANK SPACE. Sometimes its 200 sometimes it s 250 sometimes is 142. So basically it’s a static range. Now the two difficult parts of this which I need your help on is 1) Finding the range on the original files from A10 to the first blank space for each file: a,b,c,d 2) Pasting them one after another with out overwriting the previous in combined.xls.

[Dave Hawley]

Change;

.  Range(Cells(1, lCol), .Cells(.Rows.Count, lCol).End(xlUp)).Copy 

To

. Range(Cells(10, lCol), .Cells(.Rows.Count, lCol).End(xlUp)).Copy