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

Sub newone()
Worksheets.Add
ActiveSheet.Range("A1").Formula = "=PrevSheet(E2)"
End Sub


Gord Dibben MS Excel MVP

On Sat, 2 Feb 2008 22:15:11 -0600, "Mark Ivey" wrote:

Maybe you could use a named range for something like this...

Mark Ivey

"I.P.Phrielie" wrote in message
...
I have an inventory of stock with formulae to calculate
"StartOfDay + Deliveries - Sales = EndOfDay"
I'm trying to write or record a macro to create a new worksheet and copy
EndOfDay from the previous days worksheet into the StartOfDay of the new
worksheet.
When I record the macro it records the actual name of the sheet ie
"Sheet4!E2" which does not give the incremental effect needed. Something
like
"Sheets.Previous!E2" is needed

Cheers!