So I did some more digging and the issue is that Excel copies the clipboard in an internal format and then when you deactivate Excel (as happens when you display the Clipboard History Switcher for example), it then copies the clipboard into an external format.
So in order to make this macro work, instead of Copy to Named Clipboard, you have replace it with a sequence like this:
- Copy
- Activate Finder
- Copy Clipboard to Named Clipboard ‘MS Excel Clipboard 1’
- Activate Microsoft Excel
Then the macro works fine.