excel 2007 enter new date

Question

I’m try to create a excel template which will have three fields I like to update automatically when it’s open.

Fields which this template will have

• Current Date: 02/07/2012
• Sheet Number: 25
• Between Date: 02/07/2012 to 02/08/2012

When I open this template after 03/08/2012 this sheet will have new data

• Current Date: 03/08/2012
• Sheet Number: 26
• Between Date: 03/08/2012 to 02/09/2012

For current date

Private Sub Workbook_Open() 
     ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Date 
End Sub

Can this be done using excel function or do I need to use VB and also how do I update template automatically with new values to say next time use these dates.

Answer 1

There are several ways to achieve this.

The easiest assumes that the start & end dates are consistent.

1. The "between" dates should be in two cells. The "start" date is set to 2nd of the current month =DATE(YEAR( NOW() ), MONTH( NOW() ), 2), the "end" date to the 2nd of the current month +1 =DATE(YEAR( NOW() ), MONTH( NOW() ) +1, 2)
2. The "current" date is then simply set to the "start" date.

However, you need to "fix" these dates when the spreadsheet is saved. Assuming that the "current" date is in cell B3 and the 2 between dates are in cells . You can use something like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Range("B3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

    Range("B5:C5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

End Sub

In the ThisWorkbook code module. This simply does a copy/past values on the cells so that the formula is replaced by the dates.

Oops, one addition is required to this. The code I've given is a little two simplistic since you would not be able to save the template so you need to wrap the given code in something that prevents it from triggering when editing the template itself. Possibly the easiest way is to use a naming convention on the files. Perhaps the template has the text "TEMPLATE " at the start of the file name.