Excel VBA FileCopy Method


I have a requirement to copy files from one folder to another. The set up is as below

Range A1 To A10 will have the file name
Range B1 to B10 will have the current folder name
Range C1 to C10 will have the destination folder name ( this is where the file should be copied to )

I want to use the FileCopy method ( or any other method for that matter ) to loop through the range B1 to B10 and copy the files to the folders mentioned in  C1 to C10

Thanks For Your help in advance.

V BSenior AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Assuming in A1 to A10 you have file name with extension it it like abc.xlsx or abc.xlsm or abc.xls whatever the extension..You can use the following code..

Sub movefiles()

    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim fso As Object
    Dim fpath As String
    Dim tpath As String

    Dim cell As Range, rng As Range, lrow As Long

    Set rng = Range("A1:A10")

    Set fso = CreateObject("scripting.filesystemobject")

    For Each cell In rng

        fpath = cell.Offset(0, 1).Value & cell.Value

        tpath = cell.Offset(0, 2).Value & cell.Value

        If fso.FileExists(tpath) Then fso.DeleteFile (tpath)
        fso.CopyFile Source:=fpath, Destination:=tpath
    Next cell

    Set fso = Nothing

End Sub

Open in new window

Now in order to run this code..You need to go to first-->Tools-->references and select the library of Microsoft scripting run time. Post which you will be good to go...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
V BSenior AnalystAuthor Commented:
Hi Saurabh,

that was fast :)

thanks this solution i what i was looking for

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.