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" <(E-Mail Removed)> wrote in message
news:5942EE46-E8ED-40CE-9C51-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote:

>Maybe you could use a named range for something like this...
>
>Mark Ivey
>
>"I.P.Phrielie" <(E-Mail Removed)> wrote in message
>news:5942EE46-E8ED-40CE-9C51-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:5942EE46-E8ED-40CE-9C51-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:5942EE46-E8ED-40CE-9C51-(E-Mail Removed)...
> >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!
>
>
>