Excel: Combining Multiple Excel Files form One Folder into a Single Worksheet

Hi All, I am trying to copy Row 2 forward on multiple excels from a single into a single worksheet. I have used this macro previously, but I keep getting a "runtime error '1004'". What am I doing wrong or could someone give me a VBA code that I could use? Sub MergeFiles() Dim path As String, ThisWB As String, lngFilecounter As Long Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet Dim Filename As String, Wkb As Workbook Dim CopyRng As Range, Dest As Range Dim RowofCopySheet As Integer RowofCopySheet = 2 ' Row to start on in the sheets you are copying from ThisWB = ActiveWorkbook.Name path = ("Folder Location") Application.EnableEvents = False Application.ScreenUpdating = False Set shtDest = ActiveWorkbook.Sheets(1) Filename = Dir(path & "\*.xlsx", vbNormal) If Len(Filename) = 0 Then Exit Sub Do Until Filename = vbNullString If Not Filename = ThisWB Then Set Wkb = Workbooks.Open(Filename:=path & "" & Filename) Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)) Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1) CopyRng.Copy Dest Wkb.Close False End If Filename = Dir() Loop Range("A1").Select Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Done!" End Sub

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of June 26, 2018.

For more resources for Microsoft Excel