How to use VBA to Copy Files

[thunder_anger]

Dear All
- I have a clients folder on Drive "D" and there are 2522 XL file numbered from 10001 to 12522
- each month i do a backup for the clients active that month
- there is a file named "LIST" that have all active clients ID
- So what i wanted to do is a way that i can copy the files that have there names on the list from "D:\clients" to "E:\ActiveThismonth"

Is there a way to do that??
thanks in advance

 

[alansidman]

From Ron deBruin's website, the following code
Copy and Move files and folders
[h=3]Copy or move more files or complete folders[/h]Note: Read the commented code lines in the code

Sub Copy_Folder()
[COLOR=black]'This example copy all files and subfolders from FromPath to ToPath.
'Note: If ToPath already exist it will overwrite existing files in this folder
'if ToPath not exist it will be made for you.[/COLOR]
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String

    FromPath = "C:\Users\Ron\Data"  [COLOR=black]'<< Change[/COLOR]
    ToPath = "C:\Users\Ron\Test"    [COLOR=black]'<< Change[/COLOR]

    [COLOR=black]'If you want to create a backup of your folder every time you run this macro
    'you can create a unique folder with a Date/Time stamp.
    'ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss")
[/COLOR]
    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath

End Sub

 

[thunder_anger]

that works for all files and folders, i looked into the link but did not get it to work. i need specific files which names are listed in a separate file

 

[alansidman]

Look at this link. I think that this may be your solution.

VBA Excel find matching list files and copy to another folder - Microsoft Community

 

[thunder_anger]

Thank you very much it works like a charm and i understood it well
this is the code

Sub CopyMyFiles()
Dim filelist As Variant
Dim i As Long
Dim filetocopy As String
Dim copysource As String
copysource = "D:\MarchBackUp\"
filelist = ActiveWorkbook.Sheets(1).Range("A1").CurrentRegion.Value
For i = 2 To UBound(filelist)
    fname = Dir$(copysource & "*.*")
    While fname <> ""
        If UCase(fname) = UCase(filelist(i, 1)) Then
            FileCopy copysource & fname, "D:\Clients\" & fname
        End If
        fname = Dir$()
    Wend
Next i
End Sub

but may i ask a question?
so if i had this
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Client Reg.[/TD]
[/TR]
[TR]
[TD]CL00012254[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]CL00012258[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]CL00025522[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]CL00011122[/TD]
[TD]Western[/TD]
[/TR]
[TR]
[TD]CL00052225[/TD]
[TD]Western[/TD]
[/TR]
</tbody>[/TABLE]
I have this tree
Drive D:
D:\Clients
D:\clients\North
D:\clients\Western
D:\clients\StoreB
D:\clients\MileB

i want to look at the client Reg. as folder name and put every file in its directory
i tried with the code but did not get it to work