VBA Copy File / Workbook

This tutorial will demonstrate how to copy a file using VBA.

VBA allows you to copy a file, using the FileSystemObject.

If you want to learn how to rename a file, you can click on this link: VBA Rename File

Copy A File / Workbook

We will show how to copy the existing file Sample file 1.xlsx in the folder VBA Folder. In this example, we won’t rename the file, just copy and overwrite it. The folder currently has only this one file:

Image 1. File in folder C:\VBA Folder

Here is the code:

Dim oFSO As Object
 
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Call oFSO.CopyFile("C:\VBA Folder\Sample file 1.xlsx", "C:\VBA Folder\", True)

You first need to create the object of the class Scripting.FileSystemObject:

Set oFSO = CreateObject("Scripting.FileSystemObject")

Then we can use the method CopyFile:

Call oFSO.CopyFile("C:\VBA Folder\Sample file 1.xlsx", "C:\VBA Folder\", True)

The first parameter of the method is the source path and the second is the destination path. The third parameter is Overwrite. As we have the same source and destination paths, we need to set Overwrite to True or False. In this example, we put True, which means that the original file is overwritten.

Let’s look now what happens if we have the same destinations, but set Overwrite to False. You just need to change this line of the code:

Call oFSO.CopyFile("C:\VBA Folder\Sample file 1.xlsx", "C:\VBA Folder\", True)

As a result, you will get an error as you can see in Image 2:

Image 2. Error when copying the file

Copy and Rename a File

Another possible option when copying a file is to rename it. It’s similar to copying a file, but now you just need to set destination path with a different name. Here is the code:

Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Call oFSO.CopyFile("C:\VBA Folder\Sample file 1.xlsx", "C:\VBA Folder\Sample file Copy.xlsx")

As you can see from the last line of the code, we want to copy file Sample file 1.xlsx in the same folder and name it Sample file Copy.xlsx:

Call oFSO.CopyFile("C:\VBA Folder\Sample file 1.xlsx", "C:\VBA Folder\Sample file Copy.xlsx")

Now we have two files in the VBA Folder. The result of the code is in Image 3:

Image 3. Copy and rename the file

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!