Combine several Excel worksheets from workbooks into one via Codes 1

[UongSaki]

I heave several Excel 2000 workbooks and each workbook has several worksheets of similar columns. Is there a way in Excel that I can combine all workbooks and their worksheets into one worksheet without having to copy and paste each worksheet?

Thank you,

UongSaki

 

[luceze]

Kind of ugly but it works.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Path As String
Dim Ext As String
Dim fName As String
Dim sht As Worksheet
Workbooks.Add
With ActiveWorkbook
.SaveAs "MergeExcelFiles"
End With
Ext = ".xls"
Path = "C:\"
fName = Dir(Path, vbNormal)
    Do While fName <> ""
            If Right(fName, 4) = Ext Then
             Workbooks.Open Filename:=Path & fName
                With ActiveWorkbook
                    For Each sht In .Sheets
                        sht.Range("A1", sht.Range("A1").SpecialCells(xlLastCell)) _
                            .Copy Workbooks("MergeExcelFiles.xls").Sheets("Sheet2") _
                            .Range("A" & Workbooks("MergeExcelFiles.xls").Sheets("Sheet2") _
                            .Range("A1").SpecialCells(xlLastCell).Row)
                    Next
                    .Close
                End With
            End If
        fName = Dir
    Loop
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

You will have to set the variable path to the directory that contains your files.

HTH,
Eric

 

[sawedoff]

Look at this link.

http://support.microsoft.com/kb/181148/EN-US/

It may help you combine the files.

Sawedoff