Copy all workbooks to one workboo

arnapsui

Board Regular
Joined
Mar 16, 2010
Messages
55
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?:):oops::eek:
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,790
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window(Alt F11). Save the workbook inside the folder with 20+ files. Run the macro
Code:
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 
Last edited:

Forum statistics

Threads
1,085,429
Messages
5,383,616
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top