Depends upon how you are adding a new sheet.

Are you copying and clearing constants from the copied sheet or just
InsertWorksheet?

Do you have a worksheet template you use for inserting?

Post back and we'll come up with something automatic.

But here is a UDF that refers to the previous sheet.

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

In a cell enter =PrevSheet(A1) to return the contents of A1 from previous
sheet.

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP


On Mon, 22 Oct 2007 10:20:02 -0700, Freida
wrote:

Back to my timesheet workbook...

I add a new sheet for each pay period (every two weeks). I name each
timesheet with the last date of the period (current sheet is 10.27.07;
previous one is 10.13.07).

To keep track of the amount of leave time accumulated I have to use cell
values from the previous periods timesheet. It is not a specific sheet; it
is relative, i.e. always the sheet preceeding the current one).

Currently, I manually change those cells to reflect the correct timesheet
name. Is there a way to refer to the relative timesheet in excel?