Author: Oscar Cronquist Article last updated on February 10, 2020

Picture of excel and txt files in windows

In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications).

I will also show you how to create folders using a macro.

To the right are links pointing to different sections in this article so you can quickly find what you are looking for.

There is also a video in this article where I show these macros in detail and how to use them.

Check out category Macro for more useful posts about VBA.

 

Copy a file

The following macro copies a file. The file name is in cell B6, the source directory is in cell B3. The destination directory is entered in cell D3.
Picture of a worksheet that allows you to copy a specific file from a source folder to a destination folder using vba

When you click the "Copy" button the macro below runs and copies file 1.jpg from folder c:\temp\src to c:\temp\dest.

'Name macro
Sub CopyFile()

'Dimension variables and declare data types
Dim src As String, dst As String, fl As String

'Save source directory specified in cell B3 to variable src
src = Range("B3")

'Save destination directory specified in cell D3 to variable dst
dst = Range("D3")

'Save file name specified in cell B3 to variable fl
fl = Range("B6")

'Enable error handling
On Error Resume Next

    'Copy file based on variables src and fl to destination folder based on variable dst and fl
    FileCopy src &  "\" &  fl, dst &  "\" &  fl

    'Check if an error has occurred
    If Err.Number <> 0 Then

        'Show error using message box
        MsgBox "Copy error: " &  src &  "\" &  fl
    End If

'Disable error handling
On Error GoTo 0

End Sub

Create a button and assign a macro to it

The macro is linked to a button shown in the picture above. To insert a button follow these steps.

  1. Go to tab "Developer" on the ribbon.
    If it is missing go to "File", click "Options". Click "Customize ribbon" and select "Developer" checkbox.
  2. Click the "Insert" button and the click "Button" in the "Form Control" group.
  3. Click and hold, then drag with mouse on the worksheet to insert a button.
  4. Excel asks for a macro to assign to the button. If you have no macro ready for the button simply click "Cancel". You can assign a macro later on.

To assign a macro to a button follow these steps.

  1. Right click on button.
  2. Click "Assign Macro..."
  3. Select a macro.
  4. Click OK.

Click the button and it will run the assigned macro.

Back to top

Where to copy the code?

  1. Copy above macro.
  2. Go to VB Editor (Visual Basic Editor) Shortcut keys Alt+F11 or go to tab "Developer" on the ribbon and click "Visual Basic" button.
    If you "Developer" tab is missing read the following article Show the Developer tab on the Microsoft website.
    Picture of where to copy and paste vba code in the vb editor
  3. Click "Insert" on the menu.
  4. Click "Module".
  5. Paste code to code module, see picture above.
  6. Exit VB Editor
  7. Save your workbook as a macro-enabled workbook (*.xlsm).
    If you don't your macro will be gone the next time you open your workbook.

Back to top

Copy and rename a file

This macro copies a file specified in cell B6 and renames the file using the value in cell D6. Cell B3 and D3 contain the source and destination paths.
Picture of a worksheet that lets you copy and rename a specific file from a source directory to a destination directory

'Name macro
Sub CopyRenameFile()

'Dimension variables and declare data types
Dim src As String, dst As String, fl As String
Dim rfl As String

'Save source directory specified in cell B3 to variable src
src = Range("B3")

'Save destination directory specified in cell D3 to variable dst
dst = Range("D3")

'Save file name specified in cell B3 to variable fl
fl = Range("B6")

'Save new file name specified in cell D6 to variable rfl
rfl = Range("D6")

'Enable error handling
On Error Resume Next

    'Copy file based on variables src and fl to destination folder based on variable dst and name file based on value in rfl
    FileCopy src &  "\" &  fl, dst &  "\" &  rfl

    'Check if an error has occurred
    If Err.Number <> 0 Then

        'Show error using message box
        MsgBox "Copy error: " &  src &  "\" &  rfl
    End If

'Disable error handling
On Error GoTo 0

End Sub

Recommended article

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Open Excel files in a folder [VBA]

Back to top

Rename a file

This macro renames file new1.jpg in folder c:\temp\dest to 2.jpg.
Picture of a worksheet that allows you to rename a file using vba macro

'Name macro
Sub RenameFile()

'Dimension variables and declare data types
Dim src As String, dst As String, fl As String
Dim rfl As String

'Save source directory specified in cell B3 to variable src
src = Range("B3")

'Save old file name specified in cell B6 to variable fl
fl = Range("B6")

'Save new file name specified in cell D6 to variable rfl
rfl = Range("D6")

'Enable error handling
On Error Resume Next

    'Rename file fl in directory src to rfl
    Name src &  "\" &  fl As src &  "\" &  rfl

    'Check if an error has occurred
    If Err.Number <> 0 Then

        'Show error using message box
        MsgBox "Error: " &  src &  "\" &  rfl
    End If
On Error GoTo 0

End Sub

Recommended article

Back to top

List files in a folder

The following macro lists files in folder c:\temp\src\

Picture of a worksheet that lists files in a folder using a vba macro

'Name macro
Sub ListFilesinFolder()

'Dimension variables and declare data types
Dim Value As String
Dim strt As Range

'Save an object reference to variable strt
Set strt = Range("B6")

'Dir function returns a string representing the name of a file or folder that matches a specified pattern.
Value = Dir(Range("B3"), &H1F)

'Loop until variable Value is empty
Do Until Value = ""

'Check if variable Value is not equal to . and ..
If Value <> "." And Value <> ".." Then
    strt = Value

    'Save an object referencing the next cell below to variable strt
    Set strt = strt.Offset(1, 0)
End If

'Repeat with next file name in folder
Value = Dir

'Keep iterating
Loop
End Sub

Recommended article

Search all workbooks in a folder

Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Search all workbooks in a folder

Back to top

List files in a folders and sub-folders

The following article explains how to list files in a folder and sub-folders. A UDF is a User Defined Function meaning a function that you can build and use in your workbooks.

List files in a folder and subfolders [UDF]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

List files in a folder and subfolders [UDF]

Watch a video where I demonstrate the macros above

The video below shows how to use the above macros in a workbook.

Back to top

How to create a folder

Picture of the vba editor containing a macro that allows you to create a folder using vba macro

The following macro creates a folder named macro at path "c:\temp".

Sub CreateFolder()
'This macro creates a folder
'named macro in path c:\temp

MkDir "c:\temp\macro"

End Sub

If the folder already exists the following error message appears.

Picture of vba error message Run-time error 75 Path/File access error

You can verify that the folder doesn't exist with the following code.

Picture of a macro that checks if a folder exist before it creates a new folder

'Name macro
Sub CheckCreateFolder()
'This macro checks if folder exists
'and if not the macro creates a folder
'named macro in path c:\temp

'Dimension variable and declare data type
Dim pth As String

'Save folder path to variable pth
pth = "c:\temp\macro"

'Check that the folder is non existing
If Dir(pth, vbDirectory) = "" Then

'Create folder
MkDir pth
End If
End Sub

Create folders using cell values

Picture of a macro that creates folders using cell values

The following macro creates directories using values in cell range B2:B4.

'Name macro
Sub CreateFolderBasedCells()
'This macro creates folders using cell values

'Iterate through each cell in cell range B2:B4 and save to variable cell
For Each cell In Range("B2:B4")

'Check if folder is non existing
If Dir(cell, vbDirectory) = "" Then

'Create folder based on content in variable cell
MkDir cell
End If

'Continue with next cell
Next cell
End Sub

The picture below displays folder names in cell range B2:B4 that I will be using in the macro above.

Picture of a worksheet containing folders to create in a cell range

This picture shows the folders in path c:\temp that the macro created.

Picture of folders

Recommended links

Download Excel file


Copy-rename-files-excel-vba.xlsm

Back to top