0

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.

|improve this question
  • As for the created date, you might want to use: =Today() in the cell. I am not clear on what else you want to accomplish – Saariko Jul 2 '12 at 13:51
  • I have tried TODAY() but it creates new date every time I'm opening the sheet, where it becomes difficult to say when this was created. – Lalajee Jul 2 '12 at 13:54
  • 1
    For today date i'm using this. Private Sub Workbook_Open() ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Date End Sub – Lalajee Jul 2 '12 at 13:58
  • When you say "Sheet number", are you referring to a page number? Where does cell F4 get the dates for the range? Typing values into the cells will not result in formula results. – CharlieRB Jul 2 '12 at 14:01
  • F4 has a dates which then gets replace every 4 weeks with new dates e.g so new dates will be 03/07/2012 to 03/08/2012 – Lalajee Jul 2 '12 at 14:08
0

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.