VBA: How to Copy File from One Location to Another

You can use the CopyFile method in VBA to copy a file from one folder to another.

Here is one common way to use this method in practice:

Sub CopyMyFile()

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
    
'specify source file and destination folder
SourceFile = "C:\Users\bob\Desktop\Some_Data_1\soccer_data.txt"
DestFolder = "C:\Users\bob\Desktop\Some_Data_2\"

'copy file
FSO.CopyFile Source:=SourceFile, Destination:=DestFolder

End Sub

This particular macro copies the file called soccer_data.txt from a folder called Some_Data_1 to a folder called Some_Data_2.

The following example shows how to use this syntax in practice.

Example: How to Copy Files Using VBA

Suppose we have a text file called soccer_data.txt located in a folder called Some_Data_1 on our Desktop:

Now suppose we would like to use VBA to copy this text file to another folder called Some_Data_2 on our Desktop, which currently contains two text files:

Before using VBA to copy this file, we need to first enable Microsoft Scripting Runtime within the VB Editor.

To do so, open the VB Editor, then click Tools, then click References:

In the new window that appears, scroll down until you see Microsoft Scripting Runtime and check the box next to it. Then click OK.

Next, we can create the following macro to copy the file:

Sub CopyMyFile()

Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
    
'specify source file and destination folder
SourceFile = "C:\Users\bob\Desktop\Some_Data_1\soccer_data.txt"
DestFolder = "C:\Users\bob\Desktop\Some_Data_2\"

'copy file
FSO.CopyFile Source:=SourceFile, Destination:=DestFolder

End Sub

Once we run this macro, the file named soccer_data.txt will be copied from the Some_Data_1 folder to the Some_Data_2 folder: