Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,573
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    3 Thread(s)

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

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,388
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

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

    You were missing a (\) backslash in the workbooks.open statement.
    Code:
    Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
    Last edited by JLGWhiz; Jun 26th, 2018 at 02:29 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  4. #4
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Neither solution worked

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,573
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    3 Thread(s)

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

    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"?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    The macro didn't run. I debugged and it didn't select my path correctly I think.

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,573
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    3 Thread(s)

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

    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?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,573
    Post Thanks / Like
    Mentioned
    70 Post(s)
    Tagged
    3 Thread(s)

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

    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"
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    U:\Documents\New folder

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •