I have spent hours searching for an answer to this and, whilst there are many posts about recovering corrupted files, there are very few about preventing them and none that fit my situation.
My team at my work used to use an old Excel 2003 sheet to record leave and travel etc. The file was stored on our central server. The problem with that was it could only store a small number of columns and whenever we got to the end of the spreadsheet it gave out error messages when inserting new data.
I redesigned the resource planner on Excel 2013 and put in some Macros for the managers to run monthly and annual reports, and to allow users to filter out certain dates and users if they choose.
The workbook is shared and, whilst this allows multiple users to access it, save data and run macros simultaneously, the workbook is becoming corrupted on a near-daily basis. The final straw was yesterday when I had to restore the previous version 6 times.
I think this must be something to do with enabling sharing on xlsm files as it was never an issue before and isn't an issue when it is not shared. Has anyone else come up against this and knows how to have the file shared without being corrupted so much?
I think one way to fix this would be to create a clone file containing no data of its own. Users would then enter data into the clone which would feed into the master document, but i feel that this is over-complicating the issue and would take a long time to build anyway.
If your macros are only for reporting and filtering then create a reporting sheet with macros that retrieves the data from the shared data workbook. The reporting sheet can be a template so every user opens a unique copy (since it doesn't need saving. Meanwhile the data entry sheet stays as an .xlsx file.