user555689's answer is a good workaround, but the actual reason this doesn't work like it used to is due to a change in the design of Excel. Because some aspects of working with multiple files behave differently, depending on whether the two workbooks are running in the same instance (or process) of Excel, the Office team decided to make Excel detect at launch whether there is already an Excel process active and, if so, merge with it, in order to achieve consistency in behavior and reduce the frequency of unexpected results. (You can see this by opening Task Manager and sorting by process name; when you launch Excel from the start menu or by opening a file on your desktop, a second Excel process will appear for a few seconds, then disappear and the memory usage of the first process will increase.)
You can read more details in this blog post by Chad Rothschiller, a Program Manager in the Excel Team, but in brief, you can achieve the functionality you're used to from Excel 2007 by manually overriding this behavior, and thus having each workbook running in its own instance of Excel.
To do this:
- hold ALT while you double-click to open the second workbook in Windows Explorer, or while you right-click the Excel icon in your taskbar and then click either "Excel 2013" or the second workbook's name in the jump list.
- Keep holding ALT the whole time, until a dialog box appears asking "Do you want to start a new instance of Excel?", then click Yes.
You now have two separate Excel processes running; now you can copy from a workbook in one and paste into the workbook in the other, and it should behave the same way that it did in Excel 2007 when you had the workbooks open in separate Excel windows.
One final note: Personally, I do recommend against always using this technique by default whenever working in multiple workbooks at once, and instead only using it when specifically needed, simply because the single-process approach offers some memory and performance benefits.
