Copy and rename multiple files using VBA Excel |

Copy and rename multiple files


To copy and rename multiple files.


In this VBA Excel automation, we are copying all the excel (.xlsx) files from a source folder to output folder using CopyFile method , and then we are renaming the files which we have copied to output folder by using Name statement . We loop through each file in source folder to rename them.


  1. Declare the variables of source and target folder
  2. Check if both the source and target folder exist
  3. Copy all the excel files to target folder using CopyFile method
  4. Loop through each file in target folder
  5. Create a string for file name which contains prefix and suffix in the original file name
  6. Rename a single excel file by Name statement
  7. Move to next file to rename


Sub CopyAllMyFiles()

    Dim objFileSystem As Object
    Dim SourceFolder As String
    Dim OriginalFile, RenamedFile As String
    Dim MyPrefix, MySuffix As String
    MyPrefix = "MyPrefix_"
    MySuffix = "_MySuffix"

    'Path of the folder where files are located
    SourceFolder = "C:\Users\yourusername\Desktop\New\Source_data\"
    TargetFolder = "C:\Users\yourusername\Desktop\New\Target_data\"

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    'Check if source and target folder exists
    If objFileSystem.FolderExists(SourceFolder) = True And objFileSystem.FolderExists(TargetFolder) = True Then
    'Copy the files to target folder
      objFileSystem.CopyFile SourceFolder & "\" & "*.xlsx", TargetFolder

    'Looping through each .xlsx file in the target folder
     For Each OriginalFile In objFileSystem.getfolder(TargetFolder).Files
    'Check if selected file is an excel file
     If InStr(OriginalFile, ".xlsx") Then
     MyFileLocation = objFileSystem.GetParentFolderName(OriginalFile)
     RenamedFile = MyFileLocation & "\" & MyPrefix & Replace(Dir(OriginalFile), ".xlsx", "") & MySuffix & ".xlsx"
     'Rename original file
     Name OriginalFile As RenamedFile
     End If
     Next OriginalFile
        MsgBox "Either source or target folder does not exist"
     End If
End Sub

Before execution of the code
, we can see that source data folder has 2 files, while target folder was empty.


move and rename file vba

Now after execution of code
, we can see that source folder has 2 files, at the same time renamed copies of both the source file have been created in target folder.


copy and rename multiple files


  1. With this approach we are keeping the original files unchanged.
  2. We can also rename any specific type of files


Post you may like

Rename folder or directory in VBA Excel |