Sheet Deactivate problem

V

vwood

Joined
May 4, 2010
Messages
4
Reaction score
0
Hi again
So I have the first part of the code working to refresh all tables. Now I am trying to also copy a range of cells and paste to a location on the same sheet I am leaving or deactivation (that is, the Forecast sheet). The copy, paste code works as a regular macro, but when included in the Worksheet, I am unable to navigate away from the sheet. It just keeps taking me back to Forecast.
I need both to happen every time I leave the Forecast sheet. I have tried several variation to no avail.
Any suggestions are welcome
and thanks in advance
Vonda

Code: 
Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False

ThisWorkbook.RefreshAll

If hasRun Then Exit Sub

Application.ScreenUpdating = False
Application.CutCopyMode = False

Sheets("Forecast").Select
Range("OI12:PV12").Select
Selection.Copy
Sheets("Forecast").Select
Cells(Range("oj11").Value, "OI").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
		:=False, Transpose:=False

hasRun = True
Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub
 
Hi Again,
So I was up in the middle of the night working on this - I know that doesn't work for some, but apparently I had a moment of clear thinking. Solved the issue
Here is the code:

Code: 
Dim lmonitor As Long

Private Sub Worksheet_Deactivate()
Application.ScreenUpdating = False
ThisWorkbook.RefreshAll
Application.ScreenUpdating = True

If lmonitor <> Range("OL10") Then
   lmonitor = Range("OL10")

Application.ScreenUpdating = False
Application.CutCopyMode = False
Range("OI12:PV12").Copy
Cells(Range("oj11").Value, "OI").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
		:=False, Transpose:=False

Application.CutCopyMode = True
Application.ScreenUpdating = True
End If
End Sub

Note: in Cell OL11 there is a sum of the range of cells involved in the copy/paste
since I want this triggered if the user changes the forecasting numbers, this works well and allows me out of the problem with not being able to navigate away from the Forecast page
V
 
Back
Top