When code runs...worksheet acts a little strange
Thank you so much!!
Now it's prefect! :)
Thanks!
"JulieD" wrote in message
...
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" wrote in message
...
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
|