On Jan 1 annually fill first 4 columns in last empty row

[Ironman]

Hi

On Jan 1 every year I need the following data input into the first 4 columns of the first empty row of Sheet 'Walking' exactly as below:

Col A: Jan 1 of the current year [equivalent to DATE(YEAR(TODAY()),1,1)]
Col B: "DUMMY ENTRY TO AVOID #REF! ERROR IN THIS SHEET AND TRAINING LOG J9 - OVERTYPE THIS ROW!" in bold and filled yellow
Col C: 1:00
Col D: 1

Book1
	A	B	C	D
103	01/01/2022	DUMMY ENTRY TO AVOID #REF! ERROR IN THIS SHEET AND TRAINING LOG J9 - OVERTYPE THIS ROW!	1:00	1.0
Walking

I'd be very grateful for a solution.

Many thanks!

 

[Ironman]

I don't know how to test this - will this code work?

Private Sub Workbook_Open()
If Date = DateSerial(Year(Now), 1, 1) Then

Dim DummyRow As Long
  
Sheet.Name = "Walking"
    DummyRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & DummyRow).Value = TODAY()
    Range("B" & DummyRow).Value = "DUMMY ENTRY TO AVOID #REF! ERROR IN THIS SHEET AND TRAINING LOG J9 - OVERTYPE THIS ROW!"
    Range("B" & DummyRow).Interior.Color = RGB(255, 255, 0)
    Range("B" & DummyRow).Font.Bold = True
    Range("C" & DummyRow).Value = "1:00"
    Range("D" & DummyRow).Value = "1"
End Sub

 

[Joe4]

Just temporarily change this line:

If Date = DateSerial(Year(Now), 1, 1) Then

to:

If Date = DateSerial(2021, 9, 8) Then

 

[Ironman]

Many thanks Joe!

What do you mean by "temporarily" please as I don't understand the difference it makes from using

Date = DateSerial(Year(Now), 1, 1)

Also, I have some more code that runs on Jan 1 every year

MsgBox "Training Log code: Uncomment (reactivate) Important Messages " & vbNewLine _
& "For Training Log F9 (Iron Man Runs This Year vs Last Year)", vbInformation, "New Year Update"
End If

Can this run using

If 1=1 Then

as well?

Thanks again!

Edit - Ah, I get it - are you saying my code is OK and how I can test it? Would you mind clarifying please?

 

[Joe4]

Yes, I assumed that the issue that you were having is that it is not January 1, so your code wouldn't run.
So just change that IF statement that determines whether or not to run to something that returns TRUE.
Note that I went back and changed the IF condition to use DateSerial, but just put in today's date. Either way should work.

And note a few other things:
1. This code needs to be put in the "ThisWorkbook" module in order to run automatically upon opening the file.
2. If you have more than one sheet, you may want to make sure that it is on the correct sheet when opening the file before updating the sheet.
3. This code will run EVERY time you open it on January 1. So if you may be opening it more than once, but only want it to run once, you should add a line of code to check your last record to see if it already is for the current date, and if it is, to not run the code again.

Let us know if you still need any assistance.

 

[Ironman]

Many thanks Joe

I just tested it and it returned the following 2 errors:

Range("A" & DummyRow).Value = TODAY()

"Compile error: Sub or Function not defined"

and

Sheet.Name = "Walking"

"Code 424: Object required"

Re your points:

1. Noted.
2. I have 20 sheets - I'm 100% certain that the code relates to the sheet 'Walking' and no other sheet. Is there something else you're telling me that I omitted to understand please?
3. Excellent point, which I'd completely overlooked, thank you Joe! Would you mind providing me with this please?

Thanks again!

 

[Ironman]

I managed to fix the 2 errors:

I changed first error row to

Range("A" & DummyRow).Value = Date

I changed second error row to

With Worksheets("Walking")

But because the Workbook_Open event opens in sheet 'Training Log' the code ran in that sheet!

How do I get it to run only in the 'Walking' sheet please?

Thanks again!

 

[Ironman]

Can anyone help me to fix this please?

Thank you!

 

[Michael M]

Today is a Formula function....in VBA it is Date().........go figure !!

Private Sub Workbook_Open()
If Date = DateSerial(Year(Now), 1, 1) Then
Dim DummyRow As Long
 With sheeets("Walking")
    DummyRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & DummyRow).Value = Date
    .Range("B" & DummyRow).Value = "DUMMY ENTRY TO AVOID #REF! ERROR IN THIS SHEET AND TRAINING LOG J9 - OVERTYPE THIS ROW!"
    .Range("B" & DummyRow).Interior.Color = RGB(255, 255, 0)
    .Range("B" & DummyRow).Font.Bold = True
    .Range("C" & DummyRow).Value = "1:00"
    .Range("D" & DummyRow).Value = "1"
End With
End Sub

 

[Ironman]

Hi Michael!

Thanks, yes, I eventually figured that out for myself (#7) but I'm stuck because the code runs in the sheet that the workbook opens in and I don't know how to fix that. I need it to run in sheet 'Walking' and no other sheet but I still want the workbook to open with the Training Log sheet.

Edit - I just noticed I didn't add "()" after Date - does that make a difference?

Thanks!