Excel (2007) VBA - Filecopy only files listed in an excel sheet to different specified folder path

[jika]

Good Morning!

I have an upload sheet which contains the following

Column A will contain either Y or N, Y if the file is to be copied upon run macro or N, if needs to be ignored
Column B - path and file name to be copied
Column C - destination path

I've been trying to figure out how to code a macro that will copy files listed in column B of the upload sheet to the destination path in column C provided that I place Y in Column A. Data in column C can vary. I also want to add the date and time in the file name of the copied file in the destination path provided. Repeat procedure until last file (Column B) with Y in Column A has been copied to the path in Column C. Here's what I got so far but it only works if a path is listed in Colum B and copies all the files from the listed path.

Anticipating thanks for your help.

Sub FileCopytest()
   Dim MySource01
   Dim MyDestination01
   Dim fn
   On Error Resume Next   ' if file is open it will not be copied
   MySource01 = Sheets("Upload").Range("B2")
   MyDestination01 = Sheets("Upload").Range("C2")
 
      If Right(MySource01, 1) <> "\" Then MySource01 = MySource01 & "\"
   If Right(MyDestination01, 1) <> "\" Then MyDestination01 = MyDestination01 & "\"
 
 
   fn = Dir(MySource01 & "*.*")
 
   Do While Not fn = ""
 
   FileCopy MySource01 & fn, MyDestination01 & fn
 
   fn = Dir()
   Loop
End Sub

 

[jika]

Hi VBA Gurus,

If you have questions about what I need to do, please don't hesitate to ask. I badly need your help on this. Thanks.