Pastespecial Triggering Worksheet_Activate to run

[jmrozins]

Greetings,

When running the code below in a new workbook, with Sheet1 activated and with application.enableevents=True, the PasteSpecial line causes the Sheet1 and Sheet2 Worksheet_Activate codes to run (as well as their respective Worksheet_Deactivate events. Moreover, when the pastespecial line is executed, I can see the screen flash over to Sheet2 and then back go Sheet1, and following the flash is the execution of the 4 events (in the expected order: Sheet1 deactivate, Sheet2 activate, Sheet2 deactivate, Sheet1 activate). I put monitors in each event to print out which worksheet is active. All 4 events report that Sheet1 is the current activesheet. It's certainly odd to have the Sheet2 activate vent tell me that Sheet1 is the activesheet.


Obviously just turning events off would keep this from happening. I have actually done so in my much larger workbook (this is just a simple example to show the concept). However, I sometime get weird errors in the more complicated workbook where the code will suddenly error-out and the workbook will unexpectedly be on Sheet2. I've been able to isolate that the only thing that could be activating Sheet 2 is the pastespecial code demonstrated here.

Note: running the code below with Sheet2 as the activesheet does not trigger any of the vents to run, or for the screen to flash in anyway -- in otherwords, the copy line is clearly working just fine without some how having to go to Sheet1 to do its job.

FYI: Cell B2 in both worksheets simply has a value of 2. I can see mathematically the pastespecial/xlMultiply is doing its job as expected.

I'm using Windows 10 and Microsoft Office Professional Plus 2016

Any thoughts? Thank you for your time!

Sub demonstration()
Sheet1.Range("B1").Copy
Sheet2.Range("B1").PasteSpecial Operation:=xlMultiply
End Sub

 

[DanteAmor]

This could be an idea

Public activating As Boolean
Sub demonstration()
    activating = True
    Sheet1.Range("B1").Copy
    Sheet2.Range("B1").PasteSpecial Operation:=xlMultiply
    activating = False
End Sub

In your events:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If activating Then Exit Sub
    '
    '...
End Sub

 

[jmrozins]

Thanks DanteAmor. However, what this really boils down to is this: The "demonstration()" sub never activates Sheet2. Yet, when the pastespecial line executes, we know that Sheet2 is indeed getting activated - whether or not the events are enabled. While Sheet2 getting activated in my simple example isn't a big deal, this behavior in my larger workbook is problematic, as the code sometimes encounters an error, and if I end the code and go back to the Excel workbook, I see that Sheet2 is the activesheet, which has further ramifications for me. So, I'm wondering why pastespecial is activating Sheet2 at all and how to keep it from happening.

 

[DanteAmor]

PasteSpecial always activates the sheets, so the recommendation to use application.screenupdating = false, but in your case does not apply, since internally excel activates the sheet to perform the pastespecial.

 

[jmrozins]

I'm sure you are right, but I wasn't able to find any documentation anywhere that states that as an inherent property of pastespecial. Can you point me to any documentation on that? I'm curious if there are other operators that also change the activesheet in this way, too, so maybe that would let me dig deeper. Thanks again!