Author: Oscar Cronquist Article last updated on August 24, 2018

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.

Sub CopyFile()
Dim src As String, dst As String, fl As String

'Source directory
src = Range("B3")

'Destination directory
dst = Range("D3")

'File name
fl = Range("B6")

On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & fl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & fl
    End If
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

Sub CopyRenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String

'Source directory
src = Range("B3")

'Destination directory
dst = Range("D3")

'File name
fl = Range("B6")

'Rename file
rfl = Range("D6")

On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Copy error: " & src & "\" & rfl
    End If
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 specified folder and create adjacent checkboxes, using vba. The […]

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

Sub RenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String

'Folder
src = Range("B3")

'File name
fl = Range("B6")

'Rename file
rfl = Range("D6")

On Error Resume Next
    Name src & "\" & fl As src & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Error: " & src & "\" & rfl
    End If
On Error GoTo 0

End Sub

Recommended article

Identify duplicate files in excel

Yesterday I demonstrated how to create a list of files in a folder and subfolders. Today I am going to […]

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

Sub ListFilesinFolder()
Dim Value As String
Dim strt As Range
Set strt = Range("B6")
Value = Dir(Range("B3"), &H1F)
Do Until Value = ""
If Value <> "." And Value <> ".." Then
    strt = Value
    Set strt = strt.Offset(1, 0)
End If
Value = Dir
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 […]

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 blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

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

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

pth = "c:\temp\macro"
If Dir(pth, vbDirectory) = "" Then
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.

Sub CreateFolderBasedCells()
'This macro creates folders using cell values
For Each cell In Range("B2:B4")
If Dir(cell, vbDirectory) = "" Then
MkDir cell
End If
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

Download excel *.xlsm file

Copy rename files (excel vba).xlsm

Back to top