VBA Macro to copy Files from a Folder to another Folder

Copying files from one folder to another folders becomes tedious and time taking task at times when moving too many files. Even there are chances to miss out any of important file to move.

Generally, If you receive multiple files to work upon and once the work on that file is completed, you now need to move that file in Completed Folder. To maintain the accuracy of too many moving files day in day out. You can automate this repetitive task simply writing a few set of VBA code in Excel.

And your task is over by just hitting a button.

I have written few lines of code and that helps me to minimize time and improve accuracy in copying files.

EXCEL VBA Scripting Object is method that you need to use to access folders from your PC.

Firstly you need to have "Microsoft Scripting Runtime" GUID (Globally Unique Identifier) enabled in your reference library. Below snapshot will show you how to enable

Click on Tool available on Ribbon of VB Editor and scroll down for "Microsoft Scripting Runtime" in Reference Library.

Let us try an example, I have a Folder with 10 JPG File that I want to copy from a MINfOLDER to Sub Main Folder

Let us start writing code.

Option Explicit
'Declare Public variables
Dim fso As New Scripting.FileSystemObject
Dim DestFldr As String

Declare all public variables explicitly, this will be accessed globally through out your module.

Declare variable fso as new Scripting.FileSystemObject, because it is instantiating collection of files.

Now write a sub procedure to assign the Source Folder and destination folder

Sub CopyAllFilesfromFolderSubFolder()
'Declare variables to store file path and folder path
    Dim FldrPath         As String
    Dim SFldr             As String

'Access Source Folder to copy files
      FldrPath = Environ("Userprofile") & "\Desktop\MINfOLDER"
'Access Destination Folder to save copied files
      DestFldr = Environ("Userprofile") & "\Desktop\MINfOLDER\Sub Main Folder"

In your case source folder and destination folder would be according to your requirement.

I have written an another sub procedure to perform copying files from MINfOLDER to MINfOLDER\Sub Main Folder and call the procedure.

Call CopyExcelFilesSub(FldrPath)

End Sub

Let me write the code

Sub CopyExcelFilesSub(startFldrPath As String)

    Dim Fname              As Scripting.File       'Holds name of file to copy
    Dim FldrOld             As Scripting.Folder   'Holds Source Folder
    Dim subfldr             As Scripting.Folder   'Holds Source Sub Folder

Dim iCtr As Integer 'Holds Counts of files copied

'Set first fso folder to access

Set FldrOld = fso.GetFolder(startFldrPath)

'Start Loop to access the folder and files having 2 characters "jp" in extension     For Each Fname In FldrOld.Files
        If Left(fso.GetExtensionName(Fname.Path), 2) = "jp" Then
            If Not fso.FileExists(Fname) Then
                    MsgBox "No File to copy..."

                ElseIf Not fso.FileExists(DestFldr & "\" & Fname.Name) Then
                    Fname.Copy DestFldr & "\" & Fname.Name

                Else
                    MsgBox "File... " & Fname.Name & " already exists"
            End If
        End If
    Next Fname

MsgBox "Files " & iCtr & " copied to destination folder successfully"

'Access Sub Folders, copy files to Destination Folder

        For Each subfldr In FldrOld.SubFolders
            Call CopyExcelFilesSub(subfldr.Path)
        Next subfldr

End Sub

See the result of Files copied