Copy contents from one excel file to another using Applescript
Using "active sheet" is your problem. Your sheet is copying from the active sheet to the active sheet, which at the time you send the copy command, are the blank cells in A1:D5 of the new workbook it created. 

Change the first 'Tell active sheet' section to:

tell workbook sourcefile to tell worksheet "Sheet1"
    tell used range
        set rc to count of rows
    end tell
    set src to range ("A1:D" &amp; rc)
end tell


For sanity's sake, I would also change the second 'tell active sheet' to:

tell workbook targetfilename to tell worksheet 1
    set dst to range ("A1:D" &amp; rc)
end tell


I made these changes and your script worked for me.