Hi
I'm using the following code (with the help of this newsgroup) to create and
update a list on a separate worrksheet. The users input the data into cells
A8:A501 of the Active worksheet, and the list is recapped (created) with no
duplicates or spaces on another worksheet (called "Adjustments") in cells
A8:A47.

Here is the code used in the Active worksheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="test"
If Not Application.Intersect(Target, _
Range("A8:A501")) Is Nothing Then
ActiveWorkbook.Sheets("Adjustments").Range("A8:A47 ").ClearContents
gCopyUnique Range("A8:A501"),
ActiveWorkbook.Sheets("Adjustments").Range("A8")
End If

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

End Sub


Here is the other part of the code that is located in the workbook's
standard module:

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

End Sub


Here is my question... This code is working great...but I need to know if
there is a way to alter the above to code to make it reference the worksheet
"before" the Active Worksheet .... instead of the "Adjustments" worksheets
as shown above.

The name of the "Adjustments" worksheet will change as the users add more
sheets to the workbook (via code in an addin file)
I can continue to use the Active worksheet part of the code above...but the
part where it references the "Adjustments" worksheet..will not work.. if the
worksheet has the name "Adjustments (2)"...and so on.. but...it will always
be the worksheet before the one were the users enter the data into..which is
the active worskheet.

Any help is greatly appreciated...
Thanks in advance!
Kimberly