Working with FILES

'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.

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

To assign a macro to a button follow these steps.

Press with right mouse button on on button.
Press with left mouse button on "Assign Macro..."
Select a macro.
Press with left mouse button on OK.

Press with left mouse button on the button and it will run the assigned macro.

Where to copy the code?

Copy above macro.
Go to VB Editor (Visual Basic Editor) Shortcut keys Alt+F11 or go to tab "Developer" on the ribbon and press with left mouse button on "Visual Basic" button.
If you "Developer" tab is missing read the following article Show the Developer tab on the Microsoft website.
Press with left mouse button on "Insert" on the menu.
Press with left mouse button on "Module".
Paste code to code module, see picture above.
Exit VB Editor
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.

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.

'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 articles

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 […]

Rename a file

This macro renames file new1.jpg in folder c:\temp\dest to 2.jpg.

'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 articles

List files in a folder

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

'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 articles

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 […]

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.

Recommended articles

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 […]

Watch a video where I demonstrate the macros above

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

How to create a folder

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.

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

'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

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.

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

Get the Excel file

Copy-rename-files-excel-vba.xlsm

Files and folders category

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 […]

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 […]

Search all workbooks in a folder and sub folders – VBA
This article demonstrates macros that allow you to search for a text string(s) in multiple worksheets and workbooks located in […]

See all articles in 'files and folders' category

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

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 […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

See all articles in 'macro' category

Vba category

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 […]

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

See all articles in 'vba' category

More than 1300 Excel formulas

Excel categories

Conditonal Formatting

Pivot Tables

VBA

Macros

UDFs

21 Responses to “Working with FILES”

Ankit says:

April 20, 2013 at 11:12 am

It was so easy to understand. I believe we can do the same by using FilesystemObject.

Reply
- Oscar says:
  
  April 22, 2013 at 9:28 am
  
  Ankit,
  
  Thank you!
  
  Yes, you are right: Creating a FileSystemObject
Bert says:

September 28, 2013 at 10:05 pm

Hi guys can the above copy and rename be modified to overwrite if file exist on destination folder? Thanks in advance

Reply
- Fabrizio says:
  
  September 14, 2014 at 9:37 pm
  
  To overwrite try
```
FileCopy src &amp; &quot;\&quot; &amp; fl, dst &amp; &quot;\&quot; &amp; fl
```
  cheers
  Fabrizio
- Fabrizio says:
  
  September 14, 2014 at 9:38 pm
  
  To overwrite try
```
FileCopy src &amp; &quot;\&quot; &amp; fl, dst &amp; &quot;\&quot; &amp; fl ,true
```
  cheers
  Fabrizio
Jens says:

October 9, 2013 at 8:04 am

Hi,

Thanks for the code, really helpfull.

How do i select multiple files for example from A1:A2000

is this possible?

Thanks in advance.

Reply
- Luis Teixeira says:
  
  June 4, 2014 at 10:33 am
  
  You know the answer? If yes can you please post here
  
  thank you
- Christian says:
  
  July 15, 2015 at 6:31 pm
  
  Hello... Even though its been years since your post I will post a link when I had the same question and it was solved.
  
  https://www.excelforum.com/excel-programming-vba-macros/1092620-copy-rename-multiple-files-in-excel.html#post4126148
Sua says:

December 26, 2013 at 8:12 am

Thanks, really helpfull

Sua
From : Indonesia

Reply
Luis Teixeira says:

June 4, 2014 at 10:30 am

Hi,

Thanks for the code, really helpfull.

How do i select multiple files for example from A1:A2000

is this possible?

Thanks in advance.

Reply
VBA Copy and Rename says:

July 22, 2014 at 6:55 pm

[…] the file to another directory. The original code was posted here, but it only works for one cell. Copy/Rename a file (excel vba) | Get Digital Help - Microsoft Excel resource Basically I have a list of file names from a directory, and those files I want to copy and rename […]

Reply
Stephen says:

July 22, 2014 at 7:17 pm

Hey Oscar,

Thanks for posting this. I am also curious how you could update the Copy and Rename VBA to work for multiple files. Any chance you could enlighten us?

-Stephen

Reply
Mohamed Monaem says:

April 16, 2015 at 12:12 pm

I Use copy files template but i need to move more than 100 file so i try to change range but there's something missing so kindly i need to code very very soon to complete my work

Reply
J.Hall says:

June 23, 2015 at 6:33 pm

This is perfect, thanks! However, like the rest of the users I'd love to copy/rename multiple files based on a cell range (e.g., D6:D100). So essentially creating a loop until the last "rename" value is reached. How should I modify the code to accomplish this?

Reply
- Christian says:
  
  July 15, 2015 at 6:32 pm
  
  https://www.excelforum.com/excel-programming-vba-macros/1092620-copy-rename-multiple-files-in-excel.html#post4126148
George says:

March 14, 2016 at 8:45 pm

what should be the codes for copying sub-folders layout from specific path into another folder, noting that the destination folder is variable based on a value inside the excel sheet.

Reply
Vikas says:

July 25, 2016 at 6:12 pm

This code does not work for me.

Let me elaborate my scenario, want to copy an excel file from ShareDrive
and paste that same file in C Drive. Do not want to change the file name.

On Error Resume Next
FileCopy sFolder & "\" & nFile, dFolder & "\" & nFile
If Err.Number 0 Then
MsgBox "Copied Successfull", vbInformation, "Status"
End If
On Error GoTo 0

Reply
JLS says:

October 24, 2017 at 8:09 am

Hi I just created a loop and used you code copy a file to a new name ".xlsm"- all worked well. However, whenever I open the files all I see is a white screen - if I move to the left an right - I can see the field/cell content. Question, why am I getting the White screen in all 50 newly created files with the following code?

lr = Cells(Rows.Count, 17).End(xlUp).Row

For x = 2 To lr
'Rename file
rfl = ThisWorkbook.Sheets("Setup").Range("Q" & x).Value

On Error Resume Next
FileCopy src, dst & rfl & ".xlsm"
If Err.Number 0 Then
MsgBox "Copy error: " & src & "\" & rfl
End If
On Error GoTo 0

Next

Reply
- Oscar says:
  
  October 25, 2017 at 5:19 pm
  
  JLS,
  
  Have you tried to open a file on another computer? Do you get the same result?
Matthias says:

July 2, 2020 at 7:56 pm

Hello,

Thanks for the copy and rename file macro. Having a slight problem with it, though.
When I used your example with the cells, a jpg file and the temp folder all is good.
As soon as I copy my folder from the desktop as txt from Win Explorer it runs into the error message. I want to copy xlsm-files.

Would you have any suggestion on that? Checked the folders again and again and also copied the old file names, therefore this shouldn't be an issue, I think. Might be the excel files to copy the issue? As the temp folder works it should be ok on the desktop as well, but nothing.

KR
Matthias

Reply
- Matthias says:
  
  July 2, 2020 at 10:29 pm
  
  Got it sorted, the extension wasn't right. Thanks again.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form