move files from 1 folder to another based on cell values

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
The following procedure moves the specified files listed in Column B from the source folder listed in Column A to the destination folder listed in Column C. If the file is moved successfully, a note to that effect will be placed in Column D. If a file does not exist within the source folder or if a file by the same name already exists within the destination folder, a note will be placed in Column D, accordingly. It also checks to see whether a path separator is included at the end of the string for the specified source and destination paths. If the path does not include a separator, it adds one at the end of the string.

Code: 
Option Explicit

Sub MoveFiles()

    Dim SourcePath As String
    Dim DestPath As String
    Dim FileName As String
    Dim LastRow As Long
    Dim i As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To LastRow
    
        FileName = Cells(i, "B").Value
    
        If Right(Cells(i, "A").Value, 1) <> Application.PathSeparator Then
            SourcePath = Cells(i, "A").Value & Application.PathSeparator
        Else
            SourcePath = Cells(i, "A").Value
        End If
        
        If Right(Cells(i, "C").Value, 1) <> Application.PathSeparator Then
            DestPath = Cells(i, "C").Value & Application.PathSeparator
        Else
            DestPath = Cells(i, "C").Value
        End If
        
        If Dir(SourcePath & FileName) = "" Then
            Cells(i, "D").Value = "Source file does not exist."
        ElseIf Dir(DestPath & FileName) <> "" Then
            Cells(i, "D").Value = "File already exists."
        Else
            Name SourcePath & FileName As DestPath & FileName
            Cells(i, "D").Value = "File moved to new location"
        End If
        
    Next i
        
End Sub
 
Last edited:
Upvote 0
Need help create destination folders if they do not exist within this VBA code.
 
Upvote 0
VBA Code: 
Sub MoveFiles()
'https://www.mrexcel.com/board/threads/move-files-from-1-folder-to-another-based-on-cell-values.490948/

Dim SourcePath As String
Dim DestPath As String
Dim FileName As String
Dim LastRow As Long
Dim i As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To LastRow
        FileName = Cells(i, "B").Value
        If Right(Cells(i, "A").Value, 1) <> Application.PathSeparator Then
            SourcePath = Cells(i, "A").Value & Application.PathSeparator
        Else
            SourcePath = Cells(i, "A").Value
        End If
        If Right(Cells(i, "C").Value, 1) <> Application.PathSeparator Then
            DestPath = Cells(i, "C").Value & Application.PathSeparator
                [B]If Len(Dir(DestPath, vbDirectory)) = 0 Then MkDir DestPath
                End If[/B]
        Else
            DestPath = Cells(i, "C").Value
        End If
        If Dir(SourcePath & FileName) = "" Then
            Cells(i, "D").Value = "Source file does not exist."
        ElseIf Dir(DestPath & FileName) <> "" Then
            Cells(i, "D").Value = "File already exists."
        Else
            Name SourcePath & FileName As DestPath & FileName
            Cells(i, "D").Value = "File moved to new location"
        End If
    Next i
End Sub


I had added the
If Len(Dir(DestPath, vbDirectory)) = 0 Then MkDir DestPath
End If
in above code to create new folders based on column C.

stackoverflow.com

Create folder path if does not exist (saving from VBA)

I have a list of items in a sheet like so: My code goes through each row and groups the supplier and copies some information into a work book for each supplier. In this scenario there are 2 unique
stackoverflow.com stackoverflow.com

I tried to implement suggestions from above link to the code.

Need Help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Locked
  • Question
VBA Code to move subfolders and all files from source folder to destination folder
Replies
1
Views
918
Peter_SSs
Peter_SSs
H
  • Question
Macro to move csv files within-subfolder to folder "Old Templates"
Replies
2
Views
392
howard
H
H
  • Question
VBA Code to Copy files from Sharedive to another folder
Replies
0
Views
383
harinsh
H
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
397
glynn1969
G
SamarthSalunkhe
  • Solved
Copy Excel From Zip Folder
Replies
9
Views
250
Domenic
D

Forum statistics

Threads
1,215,327
Messages
6,124,276
Members
449,149
Latest member
mwdbActuary

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top