Carrying formulas from one sheet to the next within the same file
 

I have 52 tabs set up, one for each week of the year. I enter data in the
top of my grid on one sheet and I want it to add it to the cumulative total
below. Each week I want to copy and paste the grid with formulas to the next
week (worksheet) but it is not carrying over the formulas. I have the name
of the referenced sheet in quotes in the formula. How do I overcome this so
I do not have to plug in the formulas every week? Thanks in advance.

Carrying formulas from one sheet to the next within the same file
 

If you're willing to use a User Defined Function.......

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

Say you have 52 sheets, sheet1 through sheet52...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP


On Mon, 19 Nov 2007 10:37:01 -0800, mrudnet
wrote:

I have 52 tabs set up, one for each week of the year. I enter data in the
top of my grid on one sheet and I want it to add it to the cumulative total
below. Each week I want to copy and paste the grid with formulas to the next
week (worksheet) but it is not carrying over the formulas. I have the name
of the referenced sheet in quotes in the formula. How do I overcome this so
I do not have to plug in the formulas every week? Thanks in advance.

Carrying formulas from one sheet to the next within the same file
 

Or, if you want to ignore Chart sheets:

Public Function PrevWorksheet(ByRef rng As Range) As Variant
Dim n As Long
Dim sName As String
Application.Volatile
With Application.Caller.Parent
sName = .Name
With .Parent.Worksheets
For n = 1 To .Count - 1
If .Item(n).Name = sName Then Exit For
Next n
If n = 1 Then
PrevWorksheet = CVErr(xlErrRef)
Else
PrevWorksheet = .Item(n - 1).Range(rng.Address).Value
End If
End With
End With
End Function


In article ,
Gord Dibben <gorddibbATshawDOTca wrote:

If you're willing to use a User Defined Function.......

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