VBA Copy File / Workbook

Associated Files Download Links

VBA allows you to copy a file, using the FileSystemObject. In this tutorial, you will learn how to copy and rename a specific file.

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:

vba-copy-file

Image 1. File in folder C:\VBA Folder

 

Here is the code:

 

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

 

Then we can use the method CopyFile:

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:

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

vba-copy-file-error

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:

 

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:

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

vba-copy-rename-file

Image 3. Copy and rename the file

You may also like some of this related content...

VBA allows you to open or close files using the standard methods .Open and .Close.
VBA allows you to check if a file or folder exists by using the Dir
VBA allows you to rename an existing file, using the Name command. In this tutorial,
VBA allows you to delete an existing file, using the Kill command. In this tutorial,
VBA allows you to choose a file to open using the Application.GetOpenFilename method. In this
Advertisements
Automate Excel
Left Menu Icon