Copy files from one location to another based off file path in column

Question

I have a list of files, with file paths, in column A.

I need to copy each file into a directory that has the same folder path (like a backup being restored).

How do I loop through column A and copy each file to column B's location?

Column A

C:\Users\user\Desktop\Test\test1\test1d.txt

C:\Users\user\Desktop\Test\test2\test2d.txt

C:\Users\user\Desktop\Test\test3\test3d.txt

...

Column B

D:\Users\user\Desktop\Test\test1\

D:\Users\user\Desktop\Test\test2\

D:\Users\user\Desktop\Test\test3\

...

I searched quite a few topics, but none were able to help me get to what I need to do.

`searched quite a few different / similar topics, but non were able to help` @tigeravatar has done this several times here at SO. — findwindow, May 11 '16 at 21:33
Can you link them to me? i mean, i've been googling all day, and then i've read through and tried about 6 or so on here myself. — mkn95u0, May 11 '16 at 21:45
Mark, that one does not do what i need it to do. that one appeared to copy directories as a form of using the vba to do a back up, not necessarily a restore... it copies them to a preset destination... and the code on top of that specifically says it will only copy 1 at a time. Maybe i'm reading that wrong, or i just don't understand the way it was put in, but it doesn't appear to be what i'm doing... — mkn95u0, May 11 '16 at 21:48
Mark, see this part, the copy from & two are static, mine are going to vary by row.Sub Copy_Folder() 'This example copy all files and subfolders from FromPath to ToPath. 'Note: If ToPath already exist it will overwrite existing files in this folder 'if ToPath not exist it will be made for you. Dim FSO As Object Dim FromPath As String Dim ToPath As String FromPath = "C:\Users\Ron\Data" '<< Change ToPath = "C:\Users\Ron\Test" '<< Change — mkn95u0, May 11 '16 at 21:50
findwindow - i just did a search for @tigeravatar, and none of his remarks even remotely come close to what i'm asking. thanks for the direction though — mkn95u0, May 11 '16 at 21:54
http://stackoverflow.com/questions/35726602/excel-vba-macro-copy-multiple-files-from-folder-to-folder does this for one row - you just need a loop. And there are a couple others that aren't quite as relevant. — Jerry Jeremiah, May 11 '16 at 22:01
Jerry - for now, this is one i'm trying to work with, so far with no luck. i'll keep the thread updated if i'm successful. I'm not aware of how to get it to loop, but if i could get it to do just one first, i can probably look into how to get the source path and dst path to loop — mkn95u0, May 11 '16 at 22:19
@mkn95u0 http://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string/18617720#18617720 — findwindow, May 11 '16 at 23:13
i posted my resolved solution in the answers. Thank you for everyone who provided valuable input. — mkn95u0, May 12 '16 at 00:06
findwindow, that is definately not related to this. those are excel formulas, not a macro. it also doesn't copy nor paste a file in any way at all. — mkn95u0, May 12 '16 at 00:09
findwindow - please read the entire question before replying next time, you'd save us all some unnecessary hassle. i believe you just looked at the column A and Column B and assumed I wanted Column B as the results, that is definitely not the case here. — mkn95u0, May 12 '16 at 00:11

score 1 · Answer 1 · answered May 11 '16 at 22:36

1

Loop through the rows and use FileCopy, something like (I am free typing this so you may need to debug)

Sub CopyFiles
Dim X as long
For X = 2 to range("A" & Rows.count).end(xlup).row 'Change 2 to 1 if you don't have headers
    FileCopy Range("A" & X).Text Range("B" & X).Text
Next
End Sub

I don't know if you will need the file name on the destination as I have never used the FileCopy function but if you do I am sure you can source it from Column A without needing help from me. Hint use Split and Ubound to get it

answered May 11 '16 at 22:36

Dan Donoghue

6,056
2
18
36

Thanks, i ended up moving the sourcepath and dst path to within the For r = section, then changing to fit the structure of the loop (range"c" 7 r) then used your & rows.count).end(xlup).row instead of the To 3000... i'll report back in a few mins. i'm able to get some of it with errors, but i think i almost have it – mkn95u0 May 11 '16 at 22:47

score 0 · Answer 2 · answered May 11 '16 at 22:38

Please try it like this.

Copy or Move one file

For one file you can use the VBA Name and FileCopy function and for entire folders or a lot of files use the other macro example's on this page.

Sub Copy_One_File()
    FileCopy "C:\Users\Ron\SourceFolder\Test.xls", "C:\Users\Ron\DestFolder\Test.xls"
End Sub

Sub Move_Rename_One_File()
'You can change the path and file name
    Name "C:\Users\Ron\SourceFolder\Test.xls" As "C:\Users\Ron\DestFolder\TestNew.xls"
End Sub

Copy or move more files or complete folders

Note: Read the commented code lines in the code

Sub Copy_Folder()
'This example copy all files and subfolders from FromPath to ToPath.
'Note: If ToPath already exist it will overwrite existing files in this folder
'if ToPath not exist it will be made for you.
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String

    FromPath = "C:\Users\Ron\Data"  '<< Change
    ToPath = "C:\Users\Ron\Test"    '<< Change

    'If you want to create a backup of your folder every time you run this macro
    'you can create a unique folder with a Date/Time stamp.
    'ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss")

    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath

End Sub

You can find all details using the link below.

http://www.rondebruin.nl/win/s3/win026.htm

score 0 · Answer 3 · edited Jan 15 '20 at 04:23

Here is the code I manipulated from excel vba macro copy multiple files from folder to folder

Sub copy()
    Dim r As Long
    Dim SourcePath As String
    Dim dstPath As String
    Dim myFile As String
    On Error GoTo ErrHandler
    For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
        SourcePath = Range("C" & r)
        dstPath = Range("D" & r)
        myFile = Range("A" & r)
        FileCopy SourcePath & "\" & myFile, dstPath & "\" & myFile
        If Range("A" & r) = "" Then
            Exit For
        End If
    Next r
    MsgBox "The file(s) can 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)"
    Range("A" & r).copy Range("F" & r)
    Resume Next
End Sub

score 0 · Answer 4 · answered Jun 18 '19 at 19:36

Below code works fine for me. But it is not able to copy files from Sub Folders

Sub copy() Dim r As Long
    Dim SourcePath As String
    Dim dstPath As String
    Dim myFile As String
    On Error GoTo ErrHandler
    For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
    SourcePath = Range("C" & r)
    dstPath = Range("D" & r)
        myFile = Range("A" & r)
        FileCopy SourcePath & "\" & myFile, dstPath & "\" & myFile
        If Range("A" & r) = "" Then
           Exit For
        End If
    Next r
        MsgBox "The file(s) can 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)" Range("A" & r).copy Range("F" & r) Resume Next End Sub

Copy files from one location to another based off file path in column

4 Answers4