Copy Methods fails intermittently - need help

[Kevlarhead]

Have had a 'beat your head on the desk until you pass out day'.

I have a spreadsheet containing a form used by engineers in my workplace for data entry. The spreadsheet was supposed to allow for mistakes to be made, so it displays a history of the previous 10 sets of details entered and allows the user to remove duplicates or other problems.

As more and more items are scanned and entered, items move down the history list. Once they reach the bottom the item is cut from the history list and moved onto one of several other lists (the scanned items are segregated by the contract they are held under). This all works fine.

What has been driving me up the wall is that I intended a similar piece of code to clear the history range when the spreadsheet is closed. This would mean all entered data gets placed into each contract's page, convenient for lookups etc. This simply doesn't work, which is very frustrating as it's practically a carbon copy of the code which removes items from the history list and place them on the relevant contract page.

Either:
The code I enter in the Before_Close worksheet event doesn't work; although it seems okay when I step through it or;

The copy/paste methods are being prevented from working by something I don't know about.

Here's the offending event: Source!D2:J11 contains the history, with the contract in col D.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim History_Row As Integer
Dim Contract As String
Dim Last_Contract_Row As Long


History_Row = 11

Do Until History_Row = 1

'Get the last row the History Range
If Sheets("Source").Cells(History_Row, 4).Value <> 0 Then

'If row is occupied read off the contract
Contract = Sheets("Source").Cells(History_Row, 4)

Last_Contract_Row = Sheets(Contract).Range("A1")

Sheets("Source").Range(("D" & History_Row & ":J" & History_Row)).Copy Sheets(Contract).Range("A" & Last_Contract_Row & ":G" & Last_Contract_Row)
Sheets("Source").Range(("D" & History_Row & ":J" & History_Row)).ClearContents
Else
End If

History_Row = History_Row - 1
Loop

ActiveWorkbook.Save
End Sub

I'm not particularly great at VBA, but this isn't a particularly complex routine. Help appreciated.

 

[Kevlarhead]

Before_Close not triggered by ActiveWorkBook.close

Okay... came back to the problem this morning and noticed what should have been obvious. The exit routine is being triggered when I close the workbook using the close icon or Ctrl-F4, but not when I use Activeworkbook.Close in my exit button.

I could get around this bu using Sendkeys(Ctrl & F4) in the exit button, but I dislike using sendkeys unless there's no alternative.

Has anyone else come across this behaviour, and is there a solution out there?

 

[Kevlarhead]

Just read the other results on the forum. I'm going with SendKeys, unless anyone has any better ideas.