Relative reference to worksheets?

[Morten Frederiksen]

Is it possible to make relative reference to a worksheet? For example make
reference to the previous worksheet without using its name explicitely. Or
make reference to a specific cell in all preceding worksheets.

Morten

 

[Frank Kabel]

Hi
you may try something like
sub foo()
dim wks_1 as worksheet
dim wks_2 as worksheet
set wks_1 = activesheet
set wks_2 = worksheets(wks_1.index-1)
msgbox wks_2.name
end sub

note: Didn't include error checking if your active sheet is the first
sheet

 

[Morten Frederiksen]

Hi
you may try something like
sub foo()
dim wks_1 as worksheet
dim wks_2 as worksheet
set wks_1 = activesheet
set wks_2 = worksheets(wks_1.index-1)
msgbox wks_2.name
end sub

note: Didn't include error checking if your active sheet is the first
sheet

Thanks, I see how this works. But can I use foo() in a formula in a cell?
For example "=foo()!A1" to get the value of cell A1 on on the previous
woorksheet? It does not seem to work for me, so maybe I am not doing it
right

Morten Frederiksen

 

[Frank Kabel]

Hi morten
try the following user defined function:
Public Function prev_worksheet(rng As Range)
Application.Volatile
Dim wks_index
wks_index = rng.Parent.Index
If wks_index = 1 Then
prev_worksheet = CVErr(xlErrValue)
Else
prev_worksheet = Worksheets(wks_index - 1).Name
End If
End Function

you may use it like
=INDIRECT("'" & prev_worksheet(A1) & "'!A1")