Use Excel VBA to Copy multiple files to different folders

I need to back up files, which may be excel or MSword, by copying them to the backup directory, say X:\backup\. Is there any vba can help to backup the files? I expect the vba can copy all file listed in column A, e.g. C:\test1\File1.xls or C:\test2\File2.doc, and then pasted to the directory in column B. e.g. X:\backup\Folder1\ or X:\backup\Folder2\. Thanks for help.

Please Login or Register  to view this content.

I have found codes from the web and it suit my requirement that all files listed in column B will be copied to the directory listed in column C. However, when any files listed in the cell of column B cannot be found, there will be error. I hope that if anyone can help to add the error handling in the macro that if the file listed in column C cannot be found, it will skip the copy of this file and excute the file in next cell and also show a message, e.g. Fail to copy, in the same row in column D. The codes are quoted as below. Thanks for help.

Sub Copy_Files()
Dim cell As Range

For Each cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 1).Value
Next cell

End Sub

I modified the copy so that it can skip the copy and show "Fail" if the source file cannot be found. The updated codes are as below. Thank all for help.

Sub Copy_Files()
Dim cell As Range


For Each cell In Range("B2", Range("B" & Rows.Count).End(xlUp))

File = Dir(cell.Value)
If Len(File) > 0 Then
FileCopy Source:=cell.Value, Destination:=cell.Offset(, 1).Value
Else
cell.Offset(, 2).Value = "Fail"
End If
Next cell


Range("A1").Select

End Sub

Hi mm1234mail,

Good evening.
Do you mind to share your .xlsm file here? It doesnt work for me based on your code given.

Thank you
YS

As this thread is 6 years old I suggest you start a new thread with all your requirements, code, sheet,etc..

Noted with many thank