Worksheets in series

B

ben simpson

What would be the easiest, and/or best way to make exact copies of my
worksheets so the totals from the first will be forwarded onto the next, so I
can keep a running total from page to page? Right now I have used the
copy>paste>link cells method, but I manually have to update each link to
refer to the last page used, and not the first.Thanks again.....Ben
 
G

Gord Dibben

Ben/Carole

Here's a User Defined Function to store in a General Module.

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
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

Group sheet2 through sheetx and in A1(or your choice) enter the formula

=PrevSheet(L16) to replicate across sheets.

DO NOT FORGET to ungroup sheets when done.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula as instructed above.


Gord Dibben Excel MVP




Just linking to your quest in hopes of a reply...(~;
Click to expand...

Gord Dibben MS Excel MVP
 
C

Carole Drake

Biig Siiiigh!
OK, I think I copied it and followed the instructions, but if I enter
=PrevSheet(B2) on sheet2, what it does is give me the formula =PrevSheet(B2)
on sheet1 and what shows is each is #NAME?

What am I doing wrong? Can you tell?
 
Top