Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


VBA Copy File – 5 examples to copy and paste

VBA Code Snippets

Visual Basic gives us access to parts of the Windows environment; therefore, there are lots of file-based activities we can achieve with VBA. One of these is to use VBA to copy a file.

In this post, we look at 5 examples of how to achieve this.

Copy a file VBA

In this post, we use the basic FileCopy method. For more advanced options, we can use the File Systems Options.

Example 1: Copy a file

The example below copies Example File.xlsx from C:\Users\marks\Documents\ to C:\Users\marks\Documents\Test\

Sub VBACopyFile()

FileCopy "C:\Users\marks\Documents\Example File.xlsx", _
    "C:\Users\marks\Documents\Test\Example File.xlsx"

End Sub

Note: In this example, the file name is unchanged.

Example 2: Copy a file using variables

In Example 1, the file names were included within the FileCopy statement. However, they can also be provided as variables.

In the code below:

  • The variables are declared (i.e., created)
  • Values are assigned to the variables
  • The variables are used in the FileCopy statement
Sub VBACopyFileVariables()

'Declare variables
Dim copyFromFile As String
Dim copyToFile As String

'Assign values to variables
copyFromFile = "C:\Users\marks\Documents\Example File.xlsx"
copyToFile = "C:\Users\marks\Documents\Example File Copied.xlsx"

'Use variables in the FileCopy statement
FileCopy copyFromFile, copyToFile

End Sub

Note: In this example, the file is not copied to a new location, but is renamed from Example File.xlsx to Example File Copied.xlsx

Example 3: Copy a file based on cell values

In this example, we copy a file using file paths contained in cell values.

Look at the screenshot below. Cell C2 contains the current file path and Cell C4 contains the path to which the file is to be copied.

VBA copy file using cell values

We can run the following macro to rename a file using these cell values.

Sub VBACopyFileSheetNames()

FileCopy ActiveSheet.Range("C2"), _
    ActiveSheet.Range("C4")

End Sub

Note: In this example, the file is copied to a new folder and also renamed from Example File.xlsx to Example File Copied.xlsx

Example 4: Check file existence before VBA copy

The FileCopy command will happily overwrite a file without showing any errors. Therefore, it is a good idea to check if a file already exists before copying over it.

The code below checks for the existence of a file in the target location. If a file exists, a message box with a Yes/No options appear. Clicking No exists the macro.

Sub VBACheckTargetFileCopyFile()

'Declare variables
Dim copyFromFile As String
Dim copyToFile As String
Dim msgBoxAnswer As Long

'Assign variables to file names
copyFromFile = "C:\Users\marks\Documents\Example File.xlsx"
copyToFile = "C:\Users\marks\Documents\Test\Example File Copied.xlsx"

'Check if the file already exists
If Dir(copyToFile) <> "" Then

    'Display message box and capture answer
    msgBoxAnswer = MsgBox(Prompt:="File already exists in that location." & _
        vbNewLine & "Do you wish to overwrite it?", Buttons:=vbYesNo, _
        Title:="Copying file")

    'If answer is No, the exist the macro
    If msgBoxAnswer = vbNo Then

        'Exit the macro
        Exit Sub

    End If

End If

'File does not exist or Yes clicked so continue
FileCopy copyFromFile, copyToFile

End Sub

Example 5: Avoiding errors when copying files

Copying files can trigger errors; the errors are outlined in the section below. The following code builds on Example 4 and also provides an error message box if any errors occur.

Sub VBAAdvancedCopyFile()

'Declare variables
Dim copyFromFile As String
Dim copyToFile As String
Dim msgBoxAnswer As Long

'Assign variables to file names
copyFromFile = "C:\Users\marks\Documents\Example File.xlsx"
copyToFile = "C:\Users\marks\Documents\Test\Example File Copied.xlsx"

On Error Resume Next

'Check if the file already exists
If Dir(copyToFile) <> "" Then

    'Display message box and capture answer
    msgBoxAnswer = MsgBox(Prompt:="File already exists in that location." & _
        vbNewLine & "Do you wish to overwrite it?", Buttons:=vbYesNo, _
        Title:="Copying file")

    'If answer is No, the exist the macro
    If msgBoxAnswer = vbNo Then

        'Exit the macro
        Exit Sub

    End If

End If

'File does not exist or Yes clicked so continue
FileCopy copyFromFile, copyToFile

'Display message if error occured
If Err.Number <> 0 Then
    MsgBox Prompt:="Unable to copy file", Buttons:=vbOK, _
        Title:="Copy file error"
End If

'Turn error checking back on
On Error GoTo 0

End Sub


Possible errors

Copying a file during automation can cause errors. Below are some of the common errors from the VBA copy file process.

Copying a file that does not exist triggers an error: Run-time error’53’: File not found.

VBA Rename File error

Copying a file to a file location that is locked (i.e., another user has the file open) triggers an error: Run-time error ’70’: Permission denied

Copying to a file which is already opened

Notes on copying files

To finish off this post, there are just a few things to make you aware of:

  • We have used Excel workbooks in the examples, but we can use any file type.
  • FileCopy cannot copy complete folders, only files.
  • The FileCopy command is core Visual Basic code. We find this code in other applications windows applications such as Word and PowerPoint.
  • Check out this post for more example codes to manage files and folders: VBA code to copy, move, delete and manage files.

Also, check the VBA code library for hundreds of reusable code snippets.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published.