When code runs...worksheet acts a little strange

[KimberlyC]

Hi
I'm using the following code below (with help form this group) to copy the
unique values in cell A8:A501 of the activeworksheet to cells A:14:A47 of
the Previous worksheet.
It's working good...but......after the user enters a value (presses the
enter key after entering data into a cell) on the Activeworksheet, the
worksheet knida flickers to the Previous worksheet and then back to the
Activeworksheet.
It is still working..it just looks a bit strange.
Is there a way to make this not happen?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim prevSheet As Worksheet

With Me

If .Index = 1 Then
MsgBox "No sheets to the left"
Set prevSheet = Worksheets("WC Adjustments")
Else
Set prevSheet = Worksheets(.Index - 1)
End If

.Unprotect Password:="test"

If Not Application.Intersect(Target, _
Range("A8:A501")) Is Nothing Then
prevSheet.Range("A13:A47").ClearContents
prevSheet.Unprotect Password:="test"
gCopyUnique Range("A8:A501"), prevSheet.Range("A13")
**** see below this code is in a standard module
End If

.Unprotect Password:="test"

'Range("R16:R51").Select
prevSheet.Unprotect Password:="test"
prevSheet.Range("A13:A47").Sort , _
Key1:=prevSheet.Range("A13"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End With
prevSheet.Protect Password:="test", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub



Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range)
ActiveSheet.Unprotect Password:="test"
rrngSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rrngDest, Unique:=True
ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True,
Scenarios:=True

As Always...Thanks for your help!
Kimberly

 

[JulieD]

Hi Kimberly

at the top of your code include the line
application.screenupdating = false

and at the end the line
application.screenupdating = true

this will stop the flickering

Cheers
JulieD

 

[KimberlyC]

Thank you so much!!

Now it's prefect!

Thanks!

 

[JulieD]

you're welcome and thanks for the feedback