Macro for calling the previous worksheet

  • Thread starter Thread starter Balkar
  • Start date Start date
B

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?
 
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
 
So far it looks good. Why would Application.Caller.Parent.Parent.Sheet...
work better than Application.Caller.Parent..Sheet...?
 
Because the parent is the sheet, so you have to go up one to the
parent.parent, the book, which has sheet properties.
 
Back
Top