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

You can use a VBA code to copy a file (workbook) from one folder to another or you can also copy a file to the same folder using a different name.

In this tutorial, we’re going to see how we can write code for both ways. Here you need to use the FileSystemObject that helps to access files and folders in Windows.

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 specify 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 “text-file” from the folder and pastes it to the location that we have specified in the code.

Copy a File and Rename

When you try to copy and paste a file on the same location there are high chances that VBA shows you an error. 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 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 file 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 (text-file.xlsx) from the folder and copy it to the same folder with a different name (test-file1.xlsx).

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