COPY AND PASTE CUMULATIVE VALUES TO 17 SHEETS

[F. Lawrence Kulchar]

I have a workbook with 17 consecutive sheets(sheet1 through sheet 17); I have
renamed them "WEEK1" through "WEEK17".

I run cumulative numbers in a variety of cells such as follows:

SHEET FORMULA

WEEK1 =16
WEEK2 =16 + 'WEEK1'!C3
WEEK3 =16 + 'WEEK2'!C3
WEEK4 =16 + 'WEEK3'!C3

etc., etc., etc., until the last sheet:

WEEK17 =16 + 'WEEK16'!C3

My question is as follows:

Is there a way to copy and paste all of the 17 sheets in one step so that
the above formulas appear properly (incrementally) in all the sheets in cell
C3?

In other words, instead of going from sheet1 to sheet 17, one by one, and
typing 17 different entries into my cells, can I start in sheet 2, then
somehow, highlight sheets3 through sheets17, then paste into cell C3 all of
the proper formulas?

Last point: I have at least 15 different cells on every sheet that require
this done for each of the 17 sheets.

Thank you,

FLKulchar

 

[Roger Govier]

Hi

The following code will do what you want for C3
Adapt further lines to deal with the other locations / formulae

Sub AddValuesFromPreviousSheet()
Dim i As Long

For i = 2 To 17
Sheets("Week" & i).Range("C3").Formula = _
"=Week" & i - 1 & "!C3" & "+16"
' insert other rows changing C3 to desired location and
'adjusting formula accordingly
Next
End Sub


Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[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 17 sheets, WEEK1 through WEEK17...........sheet names don't
matter.

In WEEK1 you have a value of 16 in C3

Select second sheet and SHIFT + Click last sheet

In active sheet C3 enter =16 + prevsheet(C3)

Copy to the other 15 cells.

Ungroup the sheets.


Gord Dibben MS Excel MVP

 

[Francis L. Kulchar]

MY problem is...I am NOT at all familiar with MACROs nor Visual Basic
Editor...

Now what??

Thanks,

FLKulchar

 

[Gord Dibben]

Now what?

You could read Roger's very clear instructions on where to place the macro
and how to run it.

Same instructions for storage if you wanted to use the prevsheet UDF

Except you don't "run" that, you enter it in cells as you would any
function.

See my instructions for that.


Gord