Or, if you want to ignore Chart sheets:

Public Function PrevWorksheet(ByRef rng As Range) As Variant
Dim n As Long
Dim sName As String
Application.Volatile
With Application.Caller.Parent
sName = .Name
With .Parent.Worksheets
For n = 1 To .Count - 1
If .Item(n).Name = sName Then Exit For
Next n
If n = 1 Then
PrevWorksheet = CVErr(xlErrRef)
Else
PrevWorksheet = .Item(n - 1).Range(rng.Address).Value
End If
End With
End With
End Function


In article ,
Gord Dibben <gorddibbATshawDOTca wrote:

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function