Event pocedure is preventing pasting copied cells

[SJ-NY]

Hi
I am using an event macro that executes when the sheet deactivates. The event procedure sets ranges and resets value in a series of cells.

When I copy a range of cells and leave that sheet, the event procedure executes and I lose the ability to paste the range I just copied to another sheet. I realize the execution of the macro is causing the issue. I do not know how to work around this or if it is even possible. Here is the code:
Private Sub Worksheet_Deactivate()
Dim Lastrow As Long
Dim LastColumn As Long
Set WS = Worksheets("B4")
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = Cells(7, Columns.Count).End(xlToLeft).Column
WS.Cells(1, 1).Resize(Lastrow, LastColumn).Name = "CurActRNG"
WS.Cells(1, 1).Resize(Lastrow).Name = "CurActTACRNG"
WS.Cells(5, 1).Resize(, LastColumn).Name = "CurActBURNG"
WS.Range("a1:a4").MergeCells = False
WS.Range("d5", WS.Cells(5, LastColumn)).FormulaR1C1 = "=LEFT(R[1]C,4)"
WS.Range("d5", WS.Cells(5, LastColumn)) = WS.Range("d5", WS.Cells(5, LastColumn)).Value
End Sub

Any help will be appreciated
Thanks
Steve

 

[Jonmo1]

You can put in some kind of Trigger cell, say Z1 for crude example.
When you want the code to NOT run, put an x in Z1

And the first line of your event code is
If Range("Z1") = "x" Then Exit Sub

 

[SJ-NY]

Hi
Thank you for t e quick response</SPAN>
This is a workbook distributed to many different users. The user updates the table on the sheet causing the need to reset the ranges and some other values in the table. Updating the ranges and values are critical. I am using the event procedure to guarantee everything is updated.</SPAN>
The user “may need to” copy out the company names and accounts. I am going to try a different approach. I will copy (through VBA) the company names and accounts onto a new sheet which the user can copy from.</SPAN>
If you can thing of a simpler way please let me know</SPAN>
Again </SPAN>
Thank you</SPAN>
Steve</SPAN>