how do I reference a cell in a previous sheet not by name, by orde

[I.P.Phrielie]

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!

 

[Mark Ivey]

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

Mark Ivey

 

[I.P.Phrielie]

Never mind... I worked it out thanks...

 

[Gord Dibben]

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

 

[Tim Zych]

The worksheet object as a Previous property, e.g.

Set wks = ActiveSheet.Previous

 

[I.P.Phrielie]

Thanks everyone, but,

after 2 days of trying to find the answer in typically unhelpful "Help"
files I recorded the Macro using 'Crtl+PgUp' and 'Ctrl+PgDn' keys to navigate
between the sheets instead of the mouse... the resulting code for switching
between the sheets came out as "ActiveSheet.Previous.Select" and
"ActiveSheet.Next.Select"

I stumbled across the solution 2 minutes after posting... hahaha

Hope this helps someone else