VBA: Number format and screen zoom

Hi Simon. I think you have put your finger on an Excel bug! There are some reports of data pasted into a cell - that's already been specifically formatted - refusing to display in that format. Excel has a fifteen digit limit, after which it tries to reformat the number into scientific notation to deal with it (and, indeed, render it on the screen) more easily. The zoom function often causes (and solves) some rendering artefacts on the screen, and it may be on a zoom to 75% Excel "sees" a large number and reformats it into scientific notation. Personally, I've never encountered a situation which would require this as a design feature in Excel!

Try reformatting the cell as Numbers with 0 decimal places and see if that stops the reformatting. Beyond that, a work around - involving sticking the data in another worksheet and bringing it into the master sheet by links, in my opinion - might be worth investigating.

Hope this helps,

Anthony