On Mice Herding I seldom have advice - sorry, I'm an old-style keyboard punisher...

Ctrl-C Ctrl-V works on my old '97 Excel copying from one sheet to another in the same workbook.
Referencing in such cases via indirect() and name of specific source sheet for that particular sheet in a cell
But I am very far from Excel expert - I know just a few tricks and usually can bend the problem around them.
Sometimes gets wonder at the speed I arrive at usable approx, but also often critique and hints of what I should do to make easier, more bla bla - before going that route I move data into tables ;-)

>I have tried dragging into another workbook. This works. All references from the original workbook is preserved. Thus, this means Excel does support moving everything, keeping the entire format, and keeping the original formulas for each cell. However, I have not found how to drag into another sheet.