| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is this looping
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why is this looping
I'd try putting
Application.enableevents = FALSE at the beginning and Application.enableevents = TRUE at the end. HTH, Barb Reinhardt "PFS" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why is this looping
thanks that worked!!!
but in my never ending quest to learn more, why? cheers Paul Barb Reinhardt wrote: I'd try putting Application.enableevents = FALSE at the beginning and Application.enableevents = TRUE at the end. HTH, Barb Reinhardt "PFS" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why is this looping
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) "PFS" wrote in message ups.com... thanks that worked!!! but in my never ending quest to learn more, why? cheers Paul Barb Reinhardt wrote: I'd try putting Application.enableevents = FALSE at the beginning and Application.enableevents = TRUE at the end. HTH, Barb Reinhardt "PFS" wrote: 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 |
Linear Mode
