copy file from excel list with extension to other folder

Question

I am new in VBA, so I have a list of document (with extension .pdf, .docx, etc) in excel column. What I would like to do is to copy all document in the list, from source folder to destination folder.

I already tried some code, it works but the code copy all the files in the folder instead of the file in list (The document list is only in B3:B10).

Any help really appreciated.

Thanks in advance.

Sub copyfile()

Dim r As Range
Dim Jajal As Range
Dim sourcePath As String, DestPath As String, FName As String

sourcePath = "C:\Users\"
DestPath = "H:\Users\"

For Each r In Range(Sheet6.Range("B3"), Sheet6.Range("B10")) 'the list document is in the sheet6 B3:B10
FName = Dir(sourcePath & r)
'Loop while files found
Do While FName <> ""
  'Copy the file
  FileCopy sourcePath & FName, DestPath & FName
  'Search the next file
  FName = Dir()
Loop
Next


End Sub

Answer 1

Copy Files From Range (List)

The Code

Option Explicit

' This will copy files found in a source path AND whose names
' are contained in a list (range), to a destination path,
' overwriting possible existing files.

Sub copyFiles()
    
    Const SourcePath As String = "C:\Users\"
    Const DestPath As String = "H:\Users\"
    Const ListAddress As String = "B3:B10"
    
    ' Write file list to array.
    Dim FileList As Variant: FileList = Sheet1.Range(ListAddress).Value
    
    ' 'Get' first file name.
    Dim FName As String: FName = Dir(SourcePath)
    ' 'Initiate' counter.
    Dim i As Long
    ' Loop files in SourcePath.
    Do While FName <> ""
        ' Check if file name of current file is contained in array (FileList).
        If Not IsError(Application.Match(FName, FileList, 0)) Then
            ' Count file.
            i = i + 1
            ' Copy file.
            FileCopy SourcePath & FName, DestPath & FName
        End If
        ' 'Get' next file name.
        FName = Dir()
    Loop
    
    ' Inform user.
    Select Case i
        Case 0: MsgBox "No files found", vbExclamation, "No Files"
        Case 1: MsgBox "Copied 1 file.", vbInformation, "Success"
        Case Else: MsgBox "Copied " & i & " files.", vbInformation, "Success"
    End Select

End Sub