Macro for calling the previous worksheet

[Balkar]

I've been using the function below that I found in another post. However, I
am having a problem that if I have another workbook open at the same time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I added
Application.Volatile to the function to have the function get the new data as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

 

[Bob Phillips]

Try

Function PrevSheet(rg As Range)
With Application.Caller.Parent

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

 

[Balkar]

So far it looks good. Why would Application.Caller.Parent.Parent.Sheet...
work better than Application.Caller.Parent..Sheet...?

 

[Bob Phillips]

Because the parent is the sheet, so you have to go up one to the
parent.parent, the book, which has sheet properties.