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

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

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

you're welcome and thanks for the feedback

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"KimberlyC" wrote in message
...
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