Depends what you mean by before. If you just mean the one to the left in the
tab layout then you can get that worksheet with

With Activesheet
If .Index = 1 Then
Msgbox "No sheets to the left"
Else
Set mySheet = Worksheets(.Index - 1)
End If
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KimberlyC" wrote in message
...
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