Daily Data Excel Spreadsheet Entry

You might use a spreadsheet to record a daily event. In my case it is the rainfall which I record each morning at 9am. It is a simple enough task to select the appropriate cell for the date and enter the figure but here is a simple automation so that when you open the spreadsheet the day's cell is already selected so it is just a matter of entering the data knowing it will go in the current date's cell.

Even better - at the start of a new year a new sheet is automatically created specifically for that year.

Want to try it?

First create the sheet you want as a template and name the sheet "Template" - this is mine:



Note that "B1" is "merged and centred" across the width of the table. This shows the Year eg "2020" - It will be entered automatically by the code.

The only formulas you need are for leap years and for the totals at the bottom of each data column.

To determine if the 29 February is required enter this in the appropriate cell ("D32"):

=IF(DAY(DATEVALUE(D31 & " "& D3 & " " & B1)+1)=29, 29,"")

Basically that tests whether the day after 28 February is the 29th if it is then it will enter 29 otherwise it remains blank. There are other ways of doing it but that works fine.

Once you have created the spreadsheet you need to delve into the Visual Basic editor by pressing the "Alt F11" key combination.

Now double click on "ThisWorkBook" and paste the following code into the right hand pane (from "Option Explicit" to end) NB Text after a ' are comments and do nothing.


That is it - save the Workbook and it should work as explained when you open it. Have fun!

Option Explicit

Private Sub Workbook_Open()

Dim xRow As Integer
Dim xCol As Integer
Dim bCheck As Boolean
Dim strName As String
Dim NewSheet As Worksheet

strName = Trim(Str(Year(Date)))

On Error Resume Next
bCheck = Len(Worksheets(strName).Name) > 0 'Check whether sheet exists for year
On Error GoTo 0

If bCheck = False Then
Set NewSheet = Worksheets("Template")
NewSheet.Copy before:=Sheets(1)

With Worksheets(1)
.Name = strName
.Range("B1").Value = strName
.Visible = True
.Activate
End With

End If


xCol = (Month(Date) * 2) + 1 'This assumes sheet in location as shown
xRow = Day(Date) + 3 'This assumes sheet in location as shown

Worksheets(1).Cells(xRow, xCol).Select

Worksheets(1).Activate

End Sub

Re: Daily Data Excel Spreadsheet Entry

Thank you for that, Bruce, have used it to set up sheets for Gas and Electricity consumption.

Re: Daily Data Excel Spreadsheet Entry

Glad it was useful.

Re: Daily Data Excel Spreadsheet Entry

I used it a lot for engineering calculations. A very useful tool.