How to Copy an Excel File (Workbook) using VBA – Macro Code

Copy an Excel File to a Separate Folder

Sub myMacro()

Dim myFile As Object
Set myFile = CreateObject("Scripting.FileSystemObject")
Call myFile.CopyFile("C:\Users\puneet\Desktop\folder\test-file.xlsx", "C:\Users\puneet\Desktop\", True)

End Sub

To write the above code:

1. First, you need to declare a variable.
2. Then, you need to assign the FileSystemObject to the variable you have just defined.
3. Next, you need to define the source location in the “CopyFile” method.
4. In the end, define the folder location where you want to paste it.

When you run this macro, it copies the Excel workbook “test-file” from the folder and pastes it to the location that we have specified in the code.

Make sure the destination folder path ends with a backslash (\). Without it, VBA treats the last part of the path as a file name, not a folder. The third argument (True) tells VBA to overwrite a file with the same name in the destination. If you use False and the file is already there, you get an error.

Copy a File and Rename

When you try to copy a file to the same location with the same name, VBA shows you an error, as a file can't be copied onto itself. In this situation, the best way is to rename the file while copying it.

Call myFile.CopyFile("C:\Users\puneet\Desktop\folder\test-file.xlsx", _
"C:\Users\puneet\Desktop\", True)

Above is the line of code that I used in the earlier method. Now, you need to make a change in this line only. For the destination location, you need to add the name that you want to use for the new file.

Call myFile.CopyFile("C:\Users\puneet\Desktop\folder\test-file.xlsx", _
"C:\Users\puneet\Desktop\folder\test-file1.xlsx", True)

When you run this code, it will copy the file (test-file.xlsx) from the folder and paste it to the same folder with a different name (test-file1.xlsx).

Sub myMacro()

Dim myFile As Object

Set myFile = _
CreateObject("Scripting.FileSystemObject")

Call myFile.CopyFile("C:\Users\puneet\Desktop\folder\test-file.xlsx", _
"C:\Users\puneet\Desktop\folder\test-file1.xlsx", True)

End Sub

Check If the File Exists Before You Copy It

If the source file is not there, CopyFile stops with a "File not found" error. You can check it first with the FileExists method of the same object:

Sub copy_file_if_exists()
    Dim myFile As Object
    Dim src As String
    src = "C:\Users\puneet\Desktop\folder\test-file.xlsx"
    Set myFile = CreateObject("Scripting.FileSystemObject")
    If myFile.FileExists(src) Then
        myFile.CopyFile src, "C:\Users\puneet\Desktop\", True
    Else
        MsgBox "The file doesn't exist: " & src
    End If
End Sub

Copy All Excel Files from a Folder

CopyFile also works with wildcards in the file name. The code below copies all the .xlsx files from one folder to another:

Sub copy_all_excel_files()
    Dim myFile As Object
    Set myFile = CreateObject("Scripting.FileSystemObject")
    myFile.CopyFile "C:\Users\puneet\Desktop\folder\*.xlsx", "C:\Users\puneet\Desktop\backup\", True
End Sub

You can use a wildcard only in the file name part of the source, not in the folder names. If no file matches, you get an error, and the destination folder must already exist.

Make a Copy of the Workbook You Have Open

To save a copy of the workbook that has your code, use the SaveCopyAs method. It saves a copy with the name you give and keeps the open workbook as it is:

Sub save_copy_of_this_workbook()
    ThisWorkbook.SaveCopyAs "C:\Users\puneet\Desktop\backup\" & Format(Date, "yyyy-mm-dd") & " " & ThisWorkbook.Name
End Sub

The code adds today's date to the file name, so you can run it every day as a backup. Use the same file extension as the original workbook (here, the code takes it from the workbook's name).

Copy a File with the FileCopy Statement

VBA also has a built-in FileCopy statement that doesn't need FileSystemObject. You give it the source file and the full destination file name:

Sub copy_with_filecopy()
    FileCopy "C:\Users\puneet\Desktop\folder\test-file.xlsx", "C:\Users\puneet\Desktop\test-file.xlsx"
End Sub

FileCopy gives an error if the source file is open. FileSystemObject only works on Windows, so on a Mac, use FileCopy (with Mac paths).

Frequently Asked Questions