There's few possible reasons of 1004 run-time error, but the most important is: you cannot open pdf in Excel application! Conclusion: you cannot read from pdf file!
On the first look:
1) source file hasn't been opened,
2) you did not set reference to a workbook (source file),
3) sheet does not exists or there are two or more opened files with the same sheet's name.
For further information, please see: Handle Run-Time Errors in VBA