VBA - I have a column with files paths to a pdf document , i want to copy the file from the filepath and paste into a new folder

[richie2989]

Hi,

I am fairly new to using vba, can someone help me - I have a column with all filled with filepaths to a specific pdf document, I want to copy the file using the filepath and paste into a new folder.

Hope this makes sense

 

[pbornemeier]

Option Explicit

Sub CopyFiles()
    'https://www.mrexcel.com/board/threads/vba-i-have-a-column-with-files-paths-to-a-pdf-document-i-want-to-copy-the-file-from-the-filepath-and-paste-into-a-new-folder.1151035/
    
    Dim sDestinationPath As String
    Dim rngListStart As Range
    Dim sFilePathNameExt As String
    Dim lLastRow As Long
    Dim lIndex As Long
    Dim sFileNameExt As String
    
    'This code assumes the list of FilePathNameExt to copy starts in cell A2
    '  If that is not the case change A2 in the next row to the correct range
    Set rngListStart = ActiveSheet.Range("A2")
    
    'The code also assumes that the list continues in that column and that
    '  there is nothing in that column below the last entry in the list
    
    'Replace the next row with the destination for the files
    sDestinationPath = Environ("userprofile") & "\Documents\"
    
    If Right(sDestinationPath, 1) <> "\" Then sDestinationPath = sDestinationPath & "\"
    
    lLastRow = Cells(Rows.Count, rngListStart.Column).End(xlUp).Row
    
    For lIndex = rngListStart.Row To lLastRow
        sFilePathNameExt = Cells(lIndex, rngListStart.Column).Value
        sFileNameExt = Mid(sFilePathNameExt, InStrRev(sFilePathNameExt, "\") + 1)
        If Dir(sFilePathNameExt, vbNormal) <> vbNullString Then
            FileCopy sFilePathNameExt, sDestinationPath & sFileNameExt
        End If
    Next
End Sub