have a question about merging mutiple workbooks

[rachelkou]

Hello,
I need some help with excel. I have more than twenty workbooks, inside each workbook, the columns are exactly the same with different numbers. I want to merge all workbooks into one worksheet in one workbook . (usually, I just open every workbook, then copy and paste the content into the first workbook)
Anyone knows how to do it?

Thanks so much!!!!!!!!!!!!!!!!!!!!!

 

[lionelnz]

search MrExcel as there are lots of code on this or Google.

 

[schielrn]

Welcome to the Board.

Like lionelnz said there is plenty out there if you search. Recently I went through this, so this link may help:

http://www.mrexcel.com/board2/viewtopic.php?t=297664&highlight=

This also assumes there is a header row in each workbook and all the workbooks are in the same file folder.

 

[jindon]

try

Sub test()
Dim myDir As String, fn As String, flg As Boolean
myDir = "c:\test" '<- change here
fn = Dir(myDir & "\*.xls")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        With Workbooks.Open(myDir & "\" & fn)
            With .Sheets(1).UsedRange
                If flg Then
                    .Resize(.Rows.Count-1,.Columns.Count).Offset(1).Copy
                Else
                    .Copy
                End If
                ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial
                flg = True
            End With
            .Close False
        End With
    End If
    fn = Dir()
Loop
End Sub

 

[rachelkou]

Thank you all very much for help, especially jindon! Your macro works very well and solve my problem. But, why it keeps asking me whether I want to save the large amount of data on the clickboard?
Thanks!!!!!!!!!!!!

 

[jindon]

Add following line before .Close False

Application.CutCopyMode = False

Edited

 

[rachelkou]

Thanks so much jindon!!!!!!!!