Roger

Cracked it! It's because the macro when it's run is looking at the
spreadsheet and sees that it is referencing links to spreadsheets that don't
exist. Break the links and it works perfectly. Many many thanks.

Now just one final question... is it possible so that when I paste it's
pasted as values only (without the formatting?)

Then I promise I'll leave you alone!