Quickly Compiling Data from Multiple Workbooks

[aagold]

Hello,

I have 15 + worksheets in different workbooks maintained by seperate owners that I need to be able to quickly compile onto a single sheet. All of the column headers are identical, and each sheet has the same number of rows (~400).

I have simply been copying and pasting the updated data onto a compiled sheet, but I know there must be a better way. This forum has been extremely helpful in the past...Does anyone have any ideas?

Thank you!

 

[Datsmart]

Take a look at this similar request and the provided code.
http://www.mrexcel.com/board2/viewtopic.php?t=264135
It should give you some ideas.

 

[aagold]

Thanks. I beleive this post may have what I need, but I am not especially fluent with VB. How would I modify this so that, for example, if I had multiple sheets with the same columns and number of rows within the same workbook (thinking this probably makes things easier), Sheet1 would copy/paste values to rows 1-400, Sheet 2 would do the same to 401-800, and so on?

Thanks again.

This line of code does the actual copy.

    Range("A2:O64").Copy _ 
    Destination:=basebook.Sheets("Summary").Range("A" & basebook.Sheets("Summary").Range("A65536").End(xlUp).Row + 1)

You could edit it and repeat it for each range of the worksheet you want to copy.
The way you list your data the ranges don't match. You want C3 to be copied to C3:G3. Does that mean you want the data in C3 repeated 5 times in those 5 cells?

If your data will always be in the same exact cells you could hard code the ranges into the code.

    Range("C3").Copy _ 
    Destination:=basebook.Sheets("Summary").Range("C" & basebook.Sheets("Summary").Range("C65536").End(xlUp).Row + 1)
    Range("E25").Copy _ 
    Destination:=basebook.Sheets("Summary").Range("C" & basebook.Sheets("Summary").Range("C65536").End(xlUp).Row + 1)
    Range("M19").Copy _ 
    Destination:=basebook.Sheets("Summary").Range("C" & basebook.Sheets("Summary").Range("C65536").End(xlUp).Row + 1)