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

[Guest]

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.

 

[Gord Dibben]

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

 

[JE McGimpsey]

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