Copy all workbooks to one workboo

[arnapsui]

I have around 20 workbooks that have been imported from another application to excel. all workbooks are in the same format but with different number of records. i want to import all these workbooks to a single workbook using macro. the data should be resides in one worksheet. how can i do that?

 

[Jon von der Heyden]

Hi and welcome to the board!

Some things we may need to know:

The files are better handled if they all reside in a single folder. Is this the case and what is the directory path to that folder?

Does the data in each workbook exist in the 1st sheet? Are there multiple sheets in the workbooks that needs to be consolidated?

What columns does the data occupy?

Is there a header row in each table, and is so what row?

 

[ravishankar]

Hi
Paste the following codes in the macro window(Alt F11). Save the workbook inside the folder with 20+ files. Run the macro

sub arnapsui()
Dim z  As Long, e As Long, g As Long, d As Long, h As Long
Dim f As String
d = 2
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
   For e = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Cells(e, 1) <> ActiveWorkbook.Name Then
        Cells(d, 2) = Cells(e, 1)
         Cells(1, 4) = "=Counta('" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!B:B)"
            For g = 2 To Cells(1, 4)
            For h = 1 To 10
        Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!" & Chr(h + 64) & g
                Cells(d, h + 2) = Cells(1, 3)
            Next h
            d = d + 1
            Next g
            d = d + 1
            End If
    Next e
MsgBox "collating is complete."
end sub

It lists the filenames in col A and B and data from C to L
Ravi

 

[jbeaucaire]

Ravi, the use of single-letter variables makes this excellent code extremely hard to read/understand. I know named variables are inconvenient to write out, but if the variable names indicated what purpose they serve, code like this will be grasped 10k times faster by learners.

Maybe make:

f = fName or FileNm
e = Rw or RwNumb
g = MyColumn

...and so on.

Just a thought. No offense intended, it's good stuff.