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!

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!

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

"I.P.Phrielie" wrote:

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!

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!

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

Set wks = ActiveSheet.Previous


--
Tim Zych
SF, CA

"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!

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

"Tim Zych" wrote:

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

Set wks = ActiveSheet.Previous


--
Tim Zych
SF, CA

"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!