Prev: When I search on my name "Meenie" I don't get 2010 posts
Next: Combine an And OR function
From: Gord Dibben on 29 Apr 2010 11:45
Since you have 31 sheets you may want to be doing other computations across
sheets.

This UDF can help with that.

Copy/paste this UDF to a general module in your workbook.

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

Example of usage...................

Say you have 31 sheets, sheet1 through shee31...........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

You could add your dates in the same manner.

Sheet1 A1 = 5/1/2010

Select sheets2 to 31 and in a cell enter

=PrevSheet(A1) + 1


Gord Dibben MS Excel MVP

On Thu, 29 Apr 2010 08:20:33 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>Sub Date_Increment()
>''increment a date in A1 across sheets
>Dim myDate As Date
>Dim iCtr As Long
> myDate = DateSerial(2010, 5, 1)
> For iCtr = 1 To Worksheets.Count
> With Worksheets(iCtr).Range("A1")
> .Value = myDate - 1 + iCtr
> .NumberFormat = "mm-dd-yyyy"
> End With
> Next iCtr
>End Sub
>
>
>Gord Dibben MS Excel MVP
>
>On Thu, 29 Apr 2010 07:59:02 -0700, Ann <Ann(a)discussions.microsoft.com>
>wrote:
>
>>I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
>>How can I type in the first day of the month and have each successive sheet
>>advance one day without typing it on each sheet individually?
>>Thanks so much.

First  |  Prev  | 
Pages: 1 2
Prev: When I search on my name "Meenie" I don't get 2010 posts
Next: Combine an And OR function