Relative transfer of data from the last sheet

[Mahendra]

Hi! Here is a tough one to crack!
I want to transfer data from a particular cell in one sheet to a
particular cell in the new sheet which I start a week later. That
means- I need a formula that will get data from the last sheet i.e.
(n-1) to nr. (n) sheet which I establish subsequently every week.Each
new sheet is numbered as (n+1)
Hope I have explained it such that you can understand my query!

 

[Jonathan Rynd]

Hi! Here is a tough one to crack!
I want to transfer data from a particular cell in one sheet to a
particular cell in the new sheet which I start a week later. That
means- I need a formula that will get data from the last sheet i.e.
(n-1) to nr. (n) sheet which I establish subsequently every week.Each
new sheet is numbered as (n+1)
Hope I have explained it such that you can understand my query!

How do you create/start the new sheet? Is it blank or are there things on
it?
What do you name the sheets, or do you leave them as the default name?

 

[Bernie Deitrick]

Mahendra,

You will need to use a macro.

Select the cell in the newest sheet and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
ActiveCell.Formula = _
"='" & Worksheets(ActiveSheet.Index - 1).Name & _
"'!" & ActiveCell.Address
End Sub