Find multi files in a folder.

[IceFrogBG]

Hello everyone.
I have a folder contain many files (about total ~ 70000 files and same type),
And a excel file has many sheet,each sheet in column E is list of file name
which I have to find in above folder.
I want to make a tool can find all file (file name is in E column) and copy to other folder.
My method :
- count number file name in E column ( 1 to last Row)
- with each file name I have one sub to compare this file name and each file of source folder ,if same it will copy to other folder else next file,
Loop all file of folder.
This way can work with a folder has file number not so big.
With my folder with many file it will be not response.
So everyone can support me to solve this problem.
I attach my excel file I try,
Thank everyone so much.

 

[Kenneth Hobson]

I guess all of those files are in path1? e.g. c:\Test\
And you want to copy them to path2? e.g. c:\Test\Copied\

Would it need to create path2?

What happens if the file exists in path2 already, overwrite or not?

 

[IceFrogBG]

I guess all of those files are in path1? e.g. c:\Test\
And you want to copy them to path2? e.g. c:\Test\Copied\

Would it need to create path2?

What happens if the file exists in path2 already, overwrite or not?

Hello Kenneth Hobson,
Thank you so much for your reply.
- "c:\Test\" includes all of files
- "c:\Test\Copied\" is empty folder and there are no duplicate files.
(if the file exists in path2 already, overwrite or add suffix at end of file name)
My problem is when I scan all file (by For … next or Do … Loop) excel will be not responding.

 

[Kenneth Hobson]

Untested but should be close.

Sub Copy2()
  Dim ws As Worksheet, c As Range
  Dim p1$, p2$
 
  p1 = "c:\Test\"
  p2 = "c:\Test\Copied\"
 
  On Error Resume Next
  For Each ws In Worksheets
    For Each c In ws.Range("E3", ws.Cells(Rows.Count, "E").End(xlUp))
      Kill p2 & c.Value
      FileCopy p1 & c.Value, p2 & c.Value
    Next c
  Next ws
End Sub