Quote Originally Posted by Zer0Cool View Post
Its hard to say without a sample and knowing the exact steps you took from copying until it stopped working.

Generally the error you mention is due to corruption in the workbook. Its possible the manipulations you are making or that the macro are causing these issues (or a combination of the 2). Its also possible there is an underlying issue in the original file that only surface through the additional steps you take after copying it.

Sometimes there is simply nothing that can be done with a specific file to prevent this. I often explain files on a computer like moving boxes; you can pack stuff in them and move many times. Some boxes will last "forever" and others might fall apart after 1 or 2 moves. Most of the time a box will start to fray and rip and show signs of wear before becoming completely broken, but on occasion it will just fall apart as you try to move the contents. Computer files can behave in much the same way, allowing you to store and manipulate the contents. Sometimes the file itself becomes corrupt and the symptoms give us a chance to move the contents to a new file before the file is no longer functional. This is especially true with "older" files that have been in use for years.
Oooh, analogies! I LOVE analogies; they make more sense to me than almost anything else in the world. Your explanation makes total sense to me; I really never thought of that before.

Specifically in Excel, the file corruption can sometimes be adverted by moving the contents in a very particular way to a new file. Done in such a way as to not carry over the underlying cause of the corruption this can resolve the problem with some effort. However, sometimes its possible to carry the source of the corruption over with the file which doesn't benefit you very much. I am a fan of this method of resolving corruption in older files as it also gives a good chance to review the file and decide whats still relevant, whats not and consider alternative approaches to how the data and representations are handled.
I found out about 30 minutes ago that, if I start with a whole, new workbook and just copy the contents of the original sheet, then paste/special/values onto Sheet1 of the new book (with NO formatting) things SEEM to be going along alright, at least for now.

Oddly enough you make mention of macros but then say the error references a "Book2.xlsx" which is not macro enabled (xlsx cannot store macros, instead xlsm is typically used). Was this just a oversight on your part when posting the error here or are you actually trying to save a macro enabled copy of the file as xlsx?
All of my macros are stored in my Personal Macro Workbook and I just run them from there. Don't tell me I've been doing something stupidly wrong all this time! Mostly, I've done it this way because a LOT of the macros get run on workbooks that are newly created each time and it just seemed simpler to just save the workbook as xlsx and go from there. Especially since many of the macros have been made for co-workers and you can imagine trying to get ALL of the people to remember to save as xlsm! They're used to just saving the way they've always done and would be hard to change.

Thanks

Jenny