Combining Multiple Excel Files form One Folder into a Single Worksheet

itzou

New Member
Joined
May 2, 2017
Messages
10
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try this macro. Change the file path in the code to suit your needs.
Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strextension = Dir(strPath & "*.xlsx")
    Do While strextension <> ""
        If strextension <> wkbDest.Name Then
            Set wkbSource = Workbooks.Open(strPath & strextension)
            With wkbSource
                .Sheets(1).UsedRange.Offset(1, 0).Copy wkbDest.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                .Close savechanges:=False
            End With
            strextension = Dir
        End If
    Loop
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You were missing a (\) backslash in the workbooks.open statement.
Code:
Set Wkb = Workbooks.Open(Filename:=path & [COLOR=#ff0000]"\"[/COLOR] & Filename)
 
Last edited:
Upvote 0
How did it not work? Did you get an error message? If so, what was the error and which line of code was highlighted when you clicked "Debug"?
 
Upvote 0
I tried the macro using the path in the code and it worked properly. What is the full path to the folder containing your source files?
 
Upvote 0
I am on excel 2010? Is that perhaps why? It's the full path. I have had other macros run on the path. It just didn't do what I wanted or timed out.
 
Upvote 0
The version of Excel is not an issue. Can you post full path to the folder containing your source files? For example, "C:\Test\Files"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top