Description:

Many automation tasks required to Copy Files from One Location to Another folder or directory using Excel VBA. We will see how to copy files in this example.

Copy Files from One Location to Another using Excel VBA. Will help you while handling with the file operations.

Solution(s):

We can use CopyFile method of FileSystemObject in Excel VBA. First we will check whether file is existing in a location. Then we can copy the file if it exists to a required location.

Code:

Here is the example VBA code to copy the file from one folder to another folder in Excel.

'In this Example I am Copying the File From "C:Temp" Folder to "D:Job" Folder
Sub sbCopyingAFile()

'Declare Variables
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String

'This is Your File Name which you want to Copy
sFile = "Sample.xls"

'Change to match the source folder path
sSFolder = "C:Temp"

'Change to match the destination folder path
sDFolder = "D:Job"

'Create Object
Set FSO = CreateObject("Scripting.FileSystemObject")

'Checking If File Is Located in the Source Folder
If Not FSO.FileExists(sSFolder & sFile) Then
    MsgBox "Specified File Not Found", vbInformation, "Not Found"
    
'Copying If the Same File is Not Located in the Destination Folder
ElseIf Not FSO.FileExists(sDFolder & sFile) Then
    FSO.CopyFile (sSFolder & sFile), sDFolder, True
    MsgBox "Specified File Copied Successfully", vbInformation, "Done!"
Else
    MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
End If

End Sub

Instructions:

  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Specify the required file and folder locations
  6. Press F5 to execute the code
  7. Now You should see your file is copied to specified location

Example Situations and Solutions

Here is the another Example to show you different situations while copying file from one location to another folder.

Case 1: If file does not existing in the source location

VBA Program to check if file exists or not in the specified location.

Copy Files form one location to another folder- example no input file

Case 2: If file is exists in the source location

VBA code should copy the source file to the destination folder.

Copy Files form one location to another folder- example with input file

Here is the output after executing the macro.

Copy Files form one location to another folder- example with output

Case 3: If file is already exists in the destination folder

VBA code should show message to the user as File is Already available in the destination folder.

Copy Files form one location to another folder

Code:

Here is the Example Code to explain the above situations.

'In this Example I am Copying the File From one loaction to another location
'as per the details specified in the Worksheet.

Sub sbCopyingAFileReadFromSheet()

'Declaration
Dim FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String


'This is Your File Name which you want to Copy.You can change File name at B5.
sFile = Sheets("Main").Range("B5")

'Change to match the source folder path. You can change Source Folder name at B6.
sSFolder = Sheets("Main").Range("B6")

'Change to match the destination folder path. You can change Destination Folder name at B6.
sDFolder = Sheets("Main").Range("B7")

'Create Object for File System
Set FSO = CreateObject("Scripting.FileSystemObject")

'Checking If File Is Located in the Source Folder
If Not FSO.FileExists(sSFolder & sFile) Then
    MsgBox "Specified File Not Found in Source Folder", vbInformation, "Not Found"

'Copying If the Same File is Not Located in the Destination Folder
ElseIf Not FSO.FileExists(sDFolder & sFile) Then
    FSO.CopyFile (sSFolder & sFile), sDFolder, True
    MsgBox "Specified File Copied to Destination Folder Successfully", vbInformation, "Done!"
Else
    MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
End If

End Sub

Example File:

Please Download the following example file and find the example to explain in the above situations we face while copying the file from one location to another folder.
Analysistabs – Copy Files from one location to another folder

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: Excel VBATags:

Share This Story, Choose Your Platform!

35 Comments

  1. Siemon March 24, 2014 at 6:58 PM

    Code doesnt work.

  2. PNRao March 29, 2014 at 12:26 PM

    Hi Siemon,
    Could you please explain where exactly your getting the issue.
    Thanks-PNRao!

  3. Albert April 23, 2014 at 2:36 AM

    The last example works for me, I would however like it if the search would look in the source folder and all sub folders. Then I would want it to copy a list of files, this list would be found in the Excel file, is this possible? Thanks.

  4. PNRao April 24, 2014 at 8:57 PM

    Hi Albert,

    This feature is available in our add-in, please download and use it – It is free.

    Thanks-PNRao!

  5. Risha August 14, 2014 at 8:17 PM

    Hi,
    Thanks for the code, its very helpful.
    I am looking for a similar code to sbCopyingAFileReadFromSheet, where instead of having one file only, there is a list of files. I tried using a Do Loop, but keep getting an “Object doesn’t support this property or method.
    Please help :)

  6. PNRao August 17, 2014 at 11:39 AM

    Hi Richa,

    It should work with loops. Could you please post that code here, so that I can have look and correct if there are any mistakes.

    Thanks-PNRao!

  7. Akhi September 18, 2014 at 2:41 PM

    Mr. PNRao,

    Your tutorials have helped a lot, I am curious to know if this could also be used for a list of files (From a list in excel)
    I’ve uploaded an excel sheet here http://www47.zippyshare.com/v/70981199/file.html
    Kindly let me know or if you could email me back on akhithinker@gmail.com it would be just AWESOME ! :D

    Thanks for your effort into easing affairs of humans on earth !
    Have a good day !

  8. Dhananjay November 17, 2014 at 2:40 PM

    how to copy folders from one path to another depending upon a list in excel

  9. Roman V April 7, 2015 at 6:48 PM

    How can I make this run daily based on a having the vba recognize the day and interact with the date in the file name as it gets changed.

  10. Rudi May 20, 2015 at 1:53 PM

    Hi, thank you very much for sharing this useful code.

    Please advice on a different scenario, how will the code be altered if I want to Override the Destination File?

    Thx Rudi.

  11. Steve June 18, 2015 at 5:33 AM

    Hi I had an issue with this code. I’m getting a “Permission Denied” error. I am trying to transfer the file in my work environment, but i have the credentials to access all of the files normally (i.e. without VBA). Any help appreciated.

    Thanks
    Steve

  12. DK July 15, 2015 at 12:03 AM

    Hi siemon!

    i’ not yet a pro in Macro but your work is a big help for me. i got an idea from your work and applied from the combined results of my research from the net. However, I’m really having a hard time on figuring it out to make a copy of a file to many copies and rename them based on the list. here is the the code so far. i indicated a note on the code where iim having a problem. can you also please email me in this address dickcel_2000@yahoo.com if you figured it out. really need it badly. please help me.

    Sub CopyandRenameFile()
    Dim src As String, dst As String, fl As String
    Dim rfl As String
    ‘Source directory
    src = Range(“B3″)
    ‘Destination directory
    dst = Range(“b9″)
    ‘File name
    fl = Range(“B12″)

    ‘ —->RENAME FILE AND I WANT TO HAVE A LIST. I TRIED (“D1:D10″) BUT DIDN’T WORK.
    rfl = Range(“d1″)

    On Error Resume Next
    FileCopy src & “” & fl, dst & “” & rfl
    If Err.Number 0 Then
    MsgBox “Copy error: ” & src & “” & rfl
    End If
    On Error GoTo 0
    End Sub

  13. Zoaib zaidi July 26, 2015 at 1:43 PM

    I am happy to find such a helpfull website. I want to know how if i want to copy many more files all together in one go.?

  14. Cheam Reaksa November 6, 2015 at 11:22 PM

    Hi, siemon

    Please, I would like to need your help on my VBA project. I want to copy data from multi workbook(A B C D) to main workbook(E) but from day to day my data was changed. I want my data are order WB A B C D, how VBA can help me.

    Best regard,

    C Reaksa

  15. shaif December 8, 2015 at 9:52 AM

    Hi Guys ,
    Quick question , i am facing issue within this code ,
    I changed my file type and path of file in this code and script is notable to find my file . Following are the changes

    ‘This is Your File Name which you want to Copy
    sFile = “Sample.xlsx” (changed to xlsx as my file is sample.xlsx)

    ‘Change to match the source folder path
    sSFolder = “D:Dec Work1”

    ‘Change to match the destination folder path
    sDFolder = “D:Dec Work2”

    But its not able to find my file . Can you please tell me where i am doing mistake ?

  16. Tari February 3, 2016 at 9:01 PM

    Hello Guys,

    Can you set the source folder path by choosing the file and similarly let the user browser to destination folder to copy the chosen file?

    Thanks for you help.

  17. Lee Pettersson May 11, 2016 at 12:11 PM

    Hi
    I am hoping you can help.
    I have the following Code. I am attempting to move all our daily files from a listing in excel.
    Column D has the Source, (D:Hard driveLee’s Hard DriveMy DocumentsWBD052U_PRINT01*.txt)
    Column E has Destination, (C:UsersLeeDocumentsWork1. WBD52U)

    When I run the macro I get the following error
    Run time error 13. Type mismatch.

    If I just do 1 line i.e. FromPath Range(D5:D5) ToPath Range (E5:E5) this works perfectly.

    As soon as I specify greater range it does not work.
    Thank you I really need some help.

    Sub Move_Daily_Files()
    Dim FSO, MyFile
    Dim FromPath As String
    Dim ToPath As String
    FromPath = ActiveSheet.Range(“D5:D7”)
    ToPath = ActiveSheet.Range(“E5:E7”)
    Set FSO = CreateObject(“Scripting.FileSystemObject”)
    FSO.CopyFile (FromPath), ToPath, True
    MsgBox “File Copied to Destination Folder Successfully”, vbInformation, “Done!”
    FSO.CopyFile Source:=FromPath, Destination:=ToPath
    End Sub

  18. Manish Gupta June 14, 2016 at 10:10 AM

    Hello,
    I need help with regards to a code.. where in I have a list of file names (excluding their extension) in column A2:A251. There are files with these names in a given folder. Every time I run the Macro, I want any one of the action to take place.
    a) either the file names are changes with current filename+ yesterday’s date (last working day’s date) and moved to a specific folder (folder will not change)
    OR
    b) a new folder is created with yesterday’s date (last working date) every time the macro is run and move those files into the folder

    finally delete the files from the source folder

  19. Chirpi July 1, 2016 at 4:41 PM

    Above Examples Are Helps to cope a Single File, How to move 3 or more file by This?

  20. sahadev Khetani July 15, 2016 at 12:27 PM

    Hi..
    I have a question like,
    I want to search a particular file in the source folder and then copy that file and paste it in the destination folder is that possible?
    If possible then please tell me how.

  21. Yash November 4, 2016 at 9:24 AM

    I am also facing the same issue. Pls help…

  22. Abhidarshi June 21, 2017 at 12:34 AM

    Hi Thanks for sharing the code.

    I personally find it very useful but when i tried to run it i am getting a mgs the “Specified File Not Found”. what should i do to correct he error.

    Thanks in advance.

  23. Ben Z June 19, 2018 at 8:19 PM

    Thank you, thank you, thank you, Very Helpful!!!

  24. RAJESH BAWKAR July 15, 2018 at 3:35 PM

    Hi
    Thanks for sharing useful code. I would like to copy the files from one folder to another with specific extension. As I am avoiding garbage to be copied, and I would like to copy only data files for backup.
    Would you please help me.
    Thanks in advance
    Rajesh Bawkar,

  25. FS January 15, 2019 at 12:52 AM

    Hi,

    I am a completee beginner with VBA.
    Is it possible to use wildcard with sFile?

    I get following to work just fine:

    format = Sheets(“Blad1”).Range(“A4”)
    namn = ActiveCell.Offset(i, 0)

    ‘This is Your File Name which you want to Copy
    sFile = namn & format

    But i would like to be able to move a file that starts with (name in cell) and then regardless of the suffix will be copied..
    Something like

    format = Sheets(“Blad1”).Range(“A4”)
    namn = ActiveCell.Offset(i, 0)

    ‘This is Your File Name which you want to Copy
    sFile = namn & “*” & format

    But it doesn’t work. Any idea how to make it work?

  26. mruu June 27, 2019 at 9:18 AM

    ***FIX***

    He/She left out some back-slashes.

    Put a backslash at the end of both the source and destination addresses.

    That should fix it.

  27. Pervez Ali Memon July 5, 2019 at 6:12 AM

    Copy Files from One Location to Another using Excel VBA. Will help you while handling with the file operations.

    this code is very use full for me thank you very much. is it possible during running of this code that a input box appears through which we can rename the new created file

  28. Biswajit August 9, 2019 at 5:46 PM

    Hi..
    I am a beginner, as per your example file I just change path & file name but when run the macro showing specified file not found.
    Can u please suggest me where is the wrong. for you reference.
    ‘In this Example I am Copying the File From “D:AAABBBTEST-1” Folder to “D:New folderCCCTEST-2” Folder
    Sub sbCopyingAFile()

    ‘Declare Variables
    Dim FSO
    Dim sFile As String
    Dim sSFolder As String
    Dim sDFolder As String

    ‘This is Your File Name which you want to Copy
    sFile = “CHECK.dwg”

    ‘Change to match the source folder path
    sSFolder = “D:AAABBBTEST-1”

    ‘Change to match the destination folder path
    sDFolder = “D:New folderCCCTEST-2”

    ‘Create Object
    Set FSO = CreateObject(“Scripting.FileSystemObject”)

    ‘Checking If File Is Located in the Source Folder
    If Not FSO.FileExists(sSFolder & sFile) Then
    MsgBox “Specified File Not Found”, vbInformation, “Not Found”

    ‘Copying If the Same File is Not Located in the Destination Folder
    ElseIf Not FSO.FileExists(sDFolder & sFile) Then
    FSO.CopyFile (sSFolder & sFile), sDFolder, True
    MsgBox “Specified File Copied Successfully”, vbInformation, “Done!”
    Else
    MsgBox “Specified File Already Exists In The Destination Folder”, vbExclamation, “File Already Exists”
    End If

    End Sub

  29. Saiful94 August 13, 2019 at 12:14 PM

    Possibly this reply are far too late for the question, but I will just reply for any others references in future,

    Your folder path should end with “,

    eg:- sSFolder = “D:Dec Work1” not sSFolder = “D:Dec Work1”

    This should work excellently

  30. Saiful94 August 13, 2019 at 12:17 PM

    Hello,

    I have one question, can we copy folder instead of file, and can we rename it with current date of back up.

    Thanks in advance

  31. Nadi August 22, 2019 at 9:26 AM

    Hi,

    This code is very useful and saves a lot of time.
    But, it always shows “Specified File Not Found” in my system.
    Even though the file exists.

    Please help

  32. Venugopal October 26, 2019 at 7:48 PM

    Hi Sir,

    The code worked really good,

    What if the i want to multiple files to multiple folders respectively

  33. NA January 6, 2020 at 5:48 AM

    Hi, if I wanted a directory name to be called what is in a specific cell like (A1=2017) and the file name to be created to incorporate that same value but in a fixed name how would I do that?

  34. Avinash July 20, 2020 at 5:54 PM

    Please check once that you have provided ” at the end while assigning source and Destination file path

  35. Jon August 1, 2020 at 11:52 AM

    Hi,

    Is thic code cpable of copy a bulk of files from 1 folder to another based on an Excel list given?

    Thanks.

Leave A Comment