Consolidate workbooks into one workbook

Do you only want to copy values or formula , formats , etc ??
Do you want blanks in the source skipped ?
Are the values in the source all constants or are some based on formula ?
Do all columns of data start in column A ?

Let's assume all the data start in column A, but some sheets can end in column E, some in F, etc.

I just want to copy the values, no formulas.

All the data, let's assume is values, no formulas at all

I just want to copy the data, no blanks, so this may cause diffuculty since some rows have 500 rows, and some sheets have 1000 rows.

I guess if i have to copy over blank rows, i can sort the data once all together, then sort the blank rows out. I have the macro to consolidate all the sheets once they are in the some workbook. I just need to get to that step

Ok ... one more question and then we can program the code ...
Would you like the data offset by one column so that column A can be used to ID source of data ?

In other words move all data over by one column in the Target sheet and use Column A , in target sheet , to indicate what book and what sheet that the data came from ??? Once you done looking at column A all you would need to do is delete it and all data would be where it belongs . :wink:

This code will copy all open workbooks into the activeWorkBooks ActiveSheet.
The Active Workbook must be where the code is and will not be included in the copy process.
This version does not give you a "Tracking Column" .. please let me know if you would like code modified to provide a tracking column.

Sub CopyAllOpenBooks()
Dim Sh As Worksheet
Dim Wb As Workbook
For Each Wb In Workbooks
If Not Wb.Name = ThisWorkbook.Name Then
For Each Sh In Wb.Worksheets
Sh.Range(("A1:" & Sh.Cells.SpecialCells(xlCellTypeLastCell).Address)).Copy
Range("A" & Cells.SpecialCells(xlCellTypeLastCell).Row + 1).PasteSpecial Paste:=xlPasteValues
Next Sh
End If ' WB name not this one
Next Wb
End Sub

OTHER QUESTIONS:
1. Is this a process you will need to do on a regular basis ? If so this could be set up to run automatically on a set of workbooks without the books even being opened. For example you could have the code look for all XLS books in a certain directory and get all the data imported without having to open anything but the target sheet. :wink:

I don't think I'll need a "target" column, but thanks for asking. I'll test the subroutine now to see if it works. Thanks so much

Actually, now that I think about it, what would be the code to include a source column and also to combine the books from a directory containing XLS files? Thanks!! The code works. This saves me a lot of time

Just finished doing this for another OP ...

http://www.mrexcel.com/board2/viewtopic.php?t=103386

what part of the code do I take out regarding that "Column D" routine? Again, thanks again on the previous code.

By the way, on the previous code you provided call "CopyAllBooks", what would the code be to get rid of blank rows and columns / or blank cells and "mush" them altogther.

For instance, let's say on Sheet 1, i have data from A1:A5, then data again on A10, then the next row is on row C. Anyway to make the data cram from A1:A6, then row B? Just a thought...

asudevils5150

Do you want to get rid of "both" blank rows and columns ? I'm thinking you may just want to get rid of blank rows. If you get rid of blank columns then data would get moved from column to column. Eg. you have data in your source in ColA and ColC .. if your remove blanks in columnB then final data would reside in ColA and ColB instead of the original ColA and ColC.