Carrying data over in worksheets

[roxiemayfield]

I have a workbook that has 65 worksheets. Each worksheet carries over info
from the previous worksheet. How do I insert a line in one sheet and it
carry over to the following sheets. Is there any way to do this?

 

[Gord Dibben]

What exactly do you mean by "carry over"?

A running total from sheet1 to sheet2 to sheet3 etc.?

Or you just want to insert a row in each sheet at the same time?

 

[roxiemayfield]

This is an inventory workbook. Worksheet 1's ending inventory carries over to
Worksheet 2's beginning inventory, etc.
I want to be able to insert lines for new products and have that line appear

 

[Gord Dibben]

Maybe you could use the User Defined Function PrevSheet for the "carrying
over".

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 65 sheets, sheet1 through sheet65...........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

To enter new products on each sheet you can also group the sheets and add a new
product on the activesheet, which will be replicated on each sheet.


Gord

 

[roxiemayfield]

Thank you!

Maybe you could use the User Defined Function PrevSheet for the "carrying
over".

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 65 sheets, sheet1 through sheet65...........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

To enter new products on each sheet you can also group the sheets and add a new
product on the activesheet, which will be replicated on each sheet.


Gord