Command to reference previous worksheet

D

dolphinv4

Hi,

I have a macro whereby I copy the workbook and create a
new file. There are many sheets, a, b, c....and I'd like
the sheets in cell A1 to reference the previous sheet
cell A1 + 1.

Ex: in sheet "a", A1 = 30 therefore in sheet "b", cell A1
should show 31 and sheet "c", A1 = 32....

Some of the sheets will be deleted when it comes to the
end of the billing cycle but I still want the subsequent
sheets to continue with the sequence, ie, if "c" gets
deleted, "d" should show A1 = 32. (A1 in "a" will always
be changed upon a new file is created).

Is the above possible? May I know what is the code I
should add in my macro?

Thanks!
Val
 
J

JE McGimpsey

You could try a User Defined Function (UDF):

Public Function PrevSheet(Optional rRng As Excel.Range) As Variant
Dim nIndex As Integer
Application.Volatile
If rRng Is Nothing Then Set rRng = Application.Caller
nIndex = rRng.Parent.Index
If nIndex > 1 Then
Set PrevSheet = Sheets(nIndex- 1).Range(rRng.Address)
Else
PrevSheet = CVErr(xlErrRef)
End If
End Function

enter

=PrevSheet()

in Sheet2!A1 and following sheets.
 
Top