I had a similar problem with code that worked perfectly for Excel 10 under XP but gave Run-time 1004 error when I ran the same code under Windows 7.

My code read in data from external databases (actually DBASEIV) into excel database tables. After examining the code closely I noticed that I cleared all the cells in my destination worksheet and then deleted the old copy of the database table on that sheet. The error disappeared when I deleted the table first and then cleared the cells. I suspect that I was calling a delete method on a table object that no longer existed resulting in the 1004 error. For some reason, Excel under XP tolerated this sloppy coding.

So, my advice is to check your code carefully. Error 1004 is a generic catch all type of error that gives no information. Just be aware that some aspect of sloppy coding might have been tolerated in your previous Excel environment but the new environment has decided to be less tolerant !.