Macro works fine in Excel 2003 but not in 2007

I have a spreadsheet designed to adjust golf handicaps for a small group of golfing friends and record the results in an archive. It worked fine in Excel 2003 but crashes on the second use in Excel 2007.
I have attached the spreadsheet. To see the problem go to Sheet "Scores" and click on Enter Sample Score, then "Update Handicap". The score and revised handicap will be recorded in the archive sheet.
Do the same thing again and the programme crashes with run-time error 1004. I can't see a problem?
I have looked at lots of forums and the best I can find is reference to Excel 2007 creating some "hidden variables" that don't clear properly but I can't understand it and can't see where my spreadsheet might do this.
Any help will be appreciated.

It doesn't crash for me. When you get the error, which line of code is highlighted when you hit the debug button?

I think the problem is because you are using .selection and therefore activating sheets unnecessarily,and the code is running ahead of the application.

This is the first failure
The fault occurs in Sub FormatArchive
Line

Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

When you run the debugger the code will run on without any adjustment.

"Fixing" this sub then moves the problem to the next similarly designed sub.

Getting rid of Selection in all instances will almost certainly cure this problem.

Maybe some one else can follow this up, I will look back later and have a go if your problem isn't solved.

HI Jack,

After looking through your code I have two suggestions.

You have a lot of "Application.Run" commands that go to subroutines that are in your modules. I believe you should use the "Call" command instead for these.

Secondly you have many SendKeys commands. I've found that with each SendKeys I had to follow it with a DoEvents command. http://support.microsoft.com/kb/179987 The idea is that you send a keystroke to the keyboard buffer but, because Windows may be working on another program running in the background, the keystroke goes to the wrong application or just gets lost. DoEvents tells VBA to wait until Windows OS comes back to this program (Excel) before continuing on with the VBA code.

Also.
You should remove all the Modules that have no code in them. Right click on the Module Name and Remove. You can (my style) put multiple subroutines in the same module to simplify your code.

Try this workbook, I'm still trying to clean up your macros, but check this so far.

Let me know if you find any mistakes I might have made, so they can be corrected now.

I haven't checked it in 2003 yet.

Avoid using Select and Activate wherever possible, they are seldom necessary.

You also had, and possibly still have, many repeated calls, again avoid this it only slows your code for no good reason.

The partly done workbook should point you in the right direction.

As Marvin says look at your SendKeys calls, I didn't get 'round to figuring out why you needed them.

Try organizing your subs in fewer modules and give them some useful names, grouping similar subs, etc makes them easier to find and edit, again I started to do this, but they could still be better organised.

Glad to have helped.