Results 1 to 5 of 5

Thread: Error in macro to copy multiple files from multiple folders to one folder

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    2

    Error in macro to copy multiple files from multiple folders to one folder

    Hi,
    I have made a macro to copy files from multiple folders and copy these files into one folder. I have created one table of 3 columns and 13 rows wherein column A has file name, column B has the source path on shared rive and column C has destination folder. However, i am getting an error "File could not be found in the source folder." even though the file is available.

    Sub Copy_Trial2()

    'Dimension r has been defined as integer
    'Strings are data type to hold multiple characters

    Dim r As Long
    Dim lastrow As Long
    Dim SourcePath As String
    Dim DstPath As String
    Dim myFile As String
    On Error GoTo ErrHandler
    For r = 1 To Range("A1:A13")
    SourcePath = Range("B1:B13")
    DstPath = Range("C1:C13")
    myFile = Range("A1:A13")
    FileCopy SourcePath & "\" & myFile, DstPath & "\" & myFile
    If Range("A1:A13") = "" Then
    Exit For
    End If

    Range("A1:A13").Copy Range("C1:C13")
    Next r
    MsgBox "The file(s) can be found in: " & vbNewLine & DstPath, , "Copy Completed"

    ErrHandler:
    MsgBox "Copy Error: " & SourcePath & "\" & myFile & vbNewLine & vbNewLine & "File could not be found in the source folder", , "Missing File(s)"

    End Sub

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Error in macro to copy multiple files from multiple folders to one folder

    This is the VB.NET CodeBank forum. It is not a place to post questions. It is a place to share working VB.NET code. You are wrong on two counts. As you are talking about an macro, I assume that this is VBA code. I have asked to moderators to move this thread to the correct forum. Please don't double post in the meantime.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error in macro to copy multiple files from multiple folders to one folder

    Welcome to VBForums

    Thread moved from the 'VB.Net Codebank' forum to the 'Office Development/VBA' forum.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error in macro to copy multiple files from multiple folders to one folder

    i am guessing the code should look more like
    Code:
    For r = 1 To 13
      SourcePath = cells(r, 2)
      DstPath = cells(r, 3)
      myFile = cells(r, 1)
      FileCopy SourcePath & "\" & myFile, DstPath & "\" & myFile
      If cells(r, 1) = "" Then
        Exit For
      End If
    Next r
    Range("A1:A13").Copy Range("C1")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    2

    Re: Error in macro to copy multiple files from multiple folders to one folder

    Sorry, I was not aware. I am not a technical person.

Posting Permissions

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



Click Here to Expand Forum to Full Width