Hi, Actually this is a continuation of my Copy Paste Design(my latest is "How to start pasting on the last row"). And I have this code from rbrhodes. It works fine, no errors and everything, but I want to enhance it, so I don't have to modify the code everytime I have to change the filename.

The code below copies data from one file to another. And filename in the code is constant and doesn't change.

How about if FILENAME("filename.xls") of Source File(wbSrc) depends on the filename that is written in column A3 of destination file(wbDest)?

example:
if column A3 of wbDest is 12-May-10
then wbSrc filename is 12-May-10.xls
Now if I change 12-May-10 to 13-May-10 then my filename now is 13-May-10.xls

Will you use If and Then? Or will you use lookupdate since filename is a date?

And how do you use those commands?

Option Explicit 
Sub CopyTo() 
 

    'Declare all variables - always a good idea!
   Dim lastrow As Long 
   Dim wbSrc As Worksheet 
   Dim wbDest As Worksheet 
    'Set to FALSE for speed/flicker
   Application.ScreenUpdating = False 
 

    'Type in once!
   Set wbDest = Workbooks("MasterFile.xls").Sheets("Sheet2") 
   Set wbSrc = Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all") 
 

    'Get last row of data
   lastrow = wbDest.Cells(Rows.Count, "A").End(xlUp).Row 
 

    'if lastrow > 1 then add 1 to equal first BLANK row
   If lastrow > 1 Then 
       lastrow = lastrow + 1 
   End If 
 

    'Use created objects not looooooooooooong.......names
    ' also avoids typing names repeatedly: too many chances for 'tpyos'!
   With wbSrc 
       .Range("D2:D9000").Copy 
       wbDest.Cells(lastrow, "A").PasteSpecial Paste:=xlPasteValues 
       .Range("E2:E9000").Copy 
       wbDest.Cells(lastrow, "B").PasteSpecial Paste:=xlPasteValues 
       .Range("F2:F9000").Copy 
       wbDest.Cells(lastrow, "C").PasteSpecial Paste:=xlPasteValues 
   End With 
 

    'Cleanup
   Set wbSrc = Nothing 
   Set wbDest = Nothing 
 

    'Reset
   Application.ScreenUpdating = True 
 

End Sub
Thanks!