Date fields in sequential consecutive worksheets.

Question

In each workbook I have 31 worksheets. Each sheet is an identical template that I use for my reports. My question is there a way to date the first date field and then have the rest of the sheets to be dated correctly. I currently use this workbook / worksheets for 4 different clients and each client can require upto six different worksheets.  Any help would be greatly appreciated even the knowledge that it can not be done.

Answer 1

Yes.  Assuming there is a sequence to the worksheets that also remains constant.  For the example, sheet names are simply Sheet1 through Sheet31, with Sheet1 being the first one.  Date goes into cell A1 of each sheet.

Enter a date into A1 of Sheet1, then working through the sheets in sequence (Sheet2, Sheet3, ... Sheet31) you put in a formula referencing the previous sheet in the sequence into cell A1.  In Sheet2, the formula would be

=Sheet1!A1+1

for Sheet3, the formula would be

=Sheet2+A1

and in Sheet31, the formula would be

=Sheet30+1

Alternatively, you could work through the sheets in sequence, changing the number of days to add, as:

in A1 of Sheet2, it is still

=Sheet1!A1+1

then in Sheet3 it would be

=Sheet1!A1+2

in Sheet4 it would be

=Sheet1!A1+3

on to Sheet31 where it would be

=Sheet1!A1+30

It's a little work to do up front, but since you're using a 'template' workbook, you only have to get it right once and it's a done deal forever.

Hope this helps.

Answer 2

I had the same result (#value!) until I realized my tab/worksheet wasn't called Sheet1 as in the example. I had to change the formula to match the name of my tab, so mine was

=Monday!A1+1

=Monday!A1+2

=Monday!A1+3

To answer your specific question, whether it has to be A1, no it doesn't. It's just that you need to change your formula to reference the cell with the original date.

I hope that makes sense!

Answer 3

Hi,

you can do this by using  code as well.

On Sheet1 in Cell A1 enter the date.

1.      Press ALT+F11 to open VB Ediror

2.      Right-click on Sheet1(in the Projects Window)

3.       Select View Code.

4.       Paste the code

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Cells(1, 1)

If Not IsDate(Target.Value) Then Exit Sub

For i = 2 To Worksheets.Count

Worksheets(i).Cells(1, 1).Value = Worksheets(i - 1).Cells(1, 1).Value + 1

Next i

End Sub

Every time you change the date,will automatically update all worksheets.

Note if you have the sheets: (webb, aaa,sder, qqq1, sdert, sheet1,......abc)

Sheet1 is webb

Answer 4

Hi JLLatham,

Perhaps the observation is correct, as far as workload.

(_Change event rather, than the _SelectionChange event)

However, thank you for your comments.

Answer 5

Good idea!  But I might recommend associating it with the _Change event rather than the _SelectionChange event to reduce the workload.  By using the _Change and making sure the change took place in A1 and resulted in a date entry, then the process only runs when that happens instead of everytime a different cell is chosen on the sheet.

As with yours, my code assumes that all sheets in the workbook need a date in A1 and that they are unprotected.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim anyWS As Worksheet

  Dim dayCount As Integer

  'this will update dates in all sheets when

  'A1 on this sheet is changed to a date entry

  If Target.Address <> "$A$1" Then

    Exit Sub

  End If

  'do the work if A1 holds a date now

  If IsDate(Target) Then

    dayCount = 1 ' initialize

    For Each anyWS In ThisWorkbook.Worksheets

      'change on all sheets but this one

      If anyWS.Name <> ActiveSheet.Name Then

        anyWS.Range("A1") = Target + dayCount

        dayCount = dayCount + 1

      End If

    Next

  End If

End Sub