Why is this looping

[PFS]

Hi All,

I am trying to copy some values within a sheet as I deactivate it.
When I leave the sheet, it seems to get stuck in a constant loop.

Can anybody tell me what the problem might be?

Private Sub Worksheet_Deactivate()

Range("BM5:BM44").Copy
Range("D96").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Range("BM48:BM87").Copy
Range("D95").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

Application.CutCopyMode = False

End Sub

cheers
Paul

 

[Barb Reinhardt]

I'd try putting

Application.enableevents = FALSE at the beginning and
Application.enableevents = TRUE at the end.

HTH,
Barb Reinhardt

 

[PFS]

thanks that worked!!!

but in my never ending quest to learn more, why?

cheers
Paul

 

[Chip Pearson]

but in my never ending quest to learn more, why?

The PasteSpecial operation causes the sheet to be activated. You can see
this if you add an Activate event procedure for the same sheet:

Private Sub Worksheet_Activate()
Debug.Print "Activate"
End Sub

The sheet is activated on the PasteSpecial and then deactivated again, which
causes the Deactivate event to run, and so on and so on. If you are not
copying formulas, you could use

Range("BM5:BM44").Copy Destination:=Range("D96")

rather than PasteSpecial. This code does not cause an Activate event.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)