Excel Workbook layout and coding philosophy

Last week I hacked into a workbook VBA code, as one of my colleagues wanted to have some additional functionality. And she paid an arm and a leg for the workbook, and the data contained in it.

So here are a couple of things that we found wrong:
1) Input data, formulas, and report functionality are all mixed together.
2) Input data for a particular customer is spread over various worksheets.
3) There are different sheets for different years, and each customer, for each year, contains formulas. 1500 formulas per customer, times 80 customers, per year. For three years, this equates to 4 Mb of data. This can be easily fixed by having one formula, and using it as required for reporting purposes.
4) Workbook size is 12 Mb. As such, it takes a long time to open, and recalculate every time you enter new data.
5) All VBA code is hardcoded to specific cell references. So don't even try and insert a column, because then half the macros will no longer work. The use of named ranges would assist here. As you add new data columns, the named ranges will move, and the code will still work.
6) All code is duplicated unnecessarily. For example, one section hides all sheets, except one. The developer copied and pasted this code, and changed the sheet visibility from False to True and vice versa for two sheets.

I can continue, but you get the idea. So what are we going to do? Redevelop it from scratch. The data is still useful, but the data store sucks.

And I would love to port the data to Access, and query it from Access from Excel. Speaking of which, I met an Access MVP yesterday morning after church. I have heard of him before, and once had an opportunity for him to work on a similar data store to the one mentioned above through another company.

So as soon as I have scoped the redesign of this package, I might get him to help.