Copy Methods fails intermittently - need help

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
Have had a 'beat your head on the desk until you pass out day'. :devilish:

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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Kevlarhead

Board Regular
Joined
May 23, 2006
Messages
176
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

Board Regular
Joined
May 23, 2006
Messages
176
Just read the other results on the forum. I'm going with SendKeys, unless anyone has any better ideas.
 

Forum statistics

Threads
1,141,718
Messages
5,708,072
Members
421,543
Latest member
SGM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top