Move or copy PDF file based on lookup table

[Twollaston]

Hello Everyone,

I have about 1050ish files that I need to move or copy into specific folders based on a lookup table. I created a lookup table that has the file name I'm looking for, the folder path where to look for it, and the folder path where it should go. The list of files to check for is about 4500 long in column A("Files to look for"), but there is only about 1050 actual files in the folder so a lot of these it's checking for won't exist(I'm assuming those will error out possibly). If someone has something that can be used for this where it can run in the background and skip the ones it doesn't find that would be a big help. Ideally I would like to move the files, but if they can only be copied over that's fine too, I can just delete the folder that holds them all right now at the end.

Does anyone have a macro that can do this? or possibly know an easy way to do it?

Here's an example of my file with the lookup table

Kind Regards,

 

[John_w]

This should do it:

Public Sub Move_Files()

    Dim r As Long
    
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Dir(.Cells(r, "B").Value & "\" & .Cells(r, "A").Value) <> vbNullString Then
                Name (.Cells(r, "B").Value & "\" & .Cells(r, "A").Value) As .Cells(r, "C").Value & "\" & .Cells(r, "A").Value
            End If
        Next
    End With
    
End Sub

 

[Twollaston]

This should do it:

Public Sub Move_Files()

    Dim r As Long
   
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If Dir(.Cells(r, "B").Value & "\" & .Cells(r, "A").Value) <> vbNullString Then
                Name (.Cells(r, "B").Value & "\" & .Cells(r, "A").Value) As .Cells(r, "C").Value & "\" & .Cells(r, "A").Value
            End If
        Next
    End With
   
End Sub

Excellent, that worked perfectly! Thanks so much for the help John, you are the man!