Message (in one window) for each file found/not found

hi guys,
need some help having a excel display a message in conjuction with this macro. i got this to work by finding the correct file in the drive and copying and pasting it to a new drive and folder. i am only stuck on having it display the message that the file was found or not found. preferrably i would like it to display the file name and whether it was found or not for each file all in the same message window. so it would look something like this: (file name) Found! Copied & Transferred to (new folder) or (file name) Not Found! thanks!

Please Login or Register  to view this content.

I think it would be in this part of the code

Please Login or Register  to view this content.

at first glance i thought it might work, but it just puts me in a perpetual loop of a "File Not Found" message

it looks like this actually goes through the source folder instead of the destination folder and lists a File Not Found message for every one in the source folder (which is in the thousands)

Norie, it looks like that runs me into a Run-time error '52' bad file name or number. the file names on my list wont always appear in the source drive in which case it should return the "not found" message. it looks like that will work when all files in filelist are accounted for in the source drive. but if even one file on the filelist can't be found, its not returning the "not found" or "found" messages. with your code here's what i have so far.

Please Login or Register  to view this content.

well i've had no problems copying the files from the folder, until one file on the filelist wasn't actually in the source folder. i'm thinking (not sure) thats what the error message is pertaining too. i'll give it a test run on some files that have all accounted for. in the meantime, would having it do the copy if the file was in the source folder and then otherwise display the "not found" message instead of do nothing make any difference?

well i've had no problems copying the files from the folder, until one file on the filelist wasn't actually in the source folder. i'm thinking (not sure) thats what the error message is pertaining too. i'll give it a test run on some files that have all accounted for. in the meantime, would having it do the copy if the file was in the source folder and then otherwise display the "not found" message instead of do nothing make any difference?

i do want them in a the same message and i actually did a test with four files in a seperate workbook (3 files which existed and 1 that didnt) and it worked fine. i wonder if it has to do with the formulas i have on the worksheet. this is the line where the error occurs.

Please Login or Register  to view this content.

the workbook itself is a data query where the data is refreshed everyday. the files i need to get are listed in a certain format (year,day,month customer name filenumber) so i entered a formula in column S reading the other columns (columns A - R) for this data and putting them in that format. i then put a formula in column T to take the values in column S (which had blanks) and list all rows that had non blank values. I've attached an example of what i'm talking about. The Columns are filtered out of view(which im not sure if it makes a difference or not)test1234.xlsm the weird part about it is it actually copies all the files (except for freeman which is the lone file that isnt in the source folder) but it fails just before the message window should appear.

i think i figured out the problem, not sure how to fix it though. column T had a long complicated formula that wasn't consistent when the data was refreshed. (rows were taken out and added). so i've deleted that column and made the filelist read column "S". however i think i need it to read only non blank cells, any ideas?

awesome works perfectly! however one problem remains that i completely forgot about. the character limit on the message box. i've shortened some of the message to fit more files on there, but it will eventually cut out when the file list gets too big. any suggestions for a work around? i'm not familiar at all with userforms if that would be the only alternative.