A lot of people use a spreadsheet to enter daily events - distance ridden on their bike, weight, rainfall etc - there are too many examples to cover them all. Usually something like this:
There are two “problems” with this (I use the word “problem” very loosely) - One is creating a new spreadtsheet for each year, the second is selecting the cell against today’s date in which you which you wish to enter the data.
Creating the basic spreadsheet as illustrated is pretty simple but why recreate it every year?
Here is the solution:
Create the sheet as illustrated and label it “Template” or any name you fancy. Then enter Visual Basic (Alt - F11), double click on “This Workbook” and enter the following code (cut and paste the lot)
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
xRow = Day(Date) + 3
Worksheets(1).Activate
Worksheets(1).Cells(xRow, xCol).Select
End Sub
There are a couple of modifications you might need to make depending on the layout of your table.
In particular these lines:
xCol = (Month(Date) * 2) + 1
xRow = Day(Date) + 3
Notice the corner of the table is at cell B3 = ie 1 column in and 3 rows down if yours starts in a different place adjust the last digit in each line accordingly
Set NewSheet = Worksheets(“Template”)
This line assumes that the sheet you are copying is called “Template”. Adjust accordingly.
Finally:
.Range(“B1”).Value = strName
This assumes that the year is in B1 ((cells merged and centered across the top of the table)
The Sheet’s Tab itself will be given the year name here:
.Name = strName
But you can modify that for example:
.Name = strName & " Weight Record"
will give you “2023 Weight Rcord”
Though personally I find the name of the file clarifies its purpose.
Anyway if all is well when you first run the file (after saving it as a .xlsm file) it should create a blank sheet from your template with this years date at the top and will continue to do so the first time it is run in the new year.
To stop people altering the “Template” once the first annual sheet is created you could “hide” it so it no longer appears in the sheet tags.
One final thing:
In the cell for the date of 29th February enter this formula and it will cope with leap years automatically
=IF(DAY(DATEVALUE(D31 & " “& D3 & " " & B1)+1)=29, 29,”")
(note that the D31,D3 and B1 values might need to be modified if the top left cell is in a different place)
Anyway have fun with that, I hope it comes in useful.
BTW I used the term Table loosely too - it is not an Excel Table
Note: I considered putting this in Technology section but it really is more of a DIY project for most people.