Insert to today's date

[Sheeloo]

You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against
the date...
You can write the code in the worksheet_activate event (signature below)
associated with the sheet containing B3
Private Sub Worksheet_Activate()
'Put your code here
'Let me know if you want me to do this
'put an IF around the line where earlier code assigns a value
'use that to change B3 to ""
End Sub

 

[SPISO]

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.

 

[SPISO]

I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code
in worksheet_activate event comment though. I am going to need help with
that.

You have been such a life saver.

 

[Sheeloo]

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

 

[Sheeloo]

Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Draw").Select
Sheets("Draw").Columns("A:A").Select
Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then
Sheets("Dashboard").Cells(3, 2) = ""
End If
Sheets("Dashboard").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


'Note: Application.EnableEvents has to be turnedoff (as done in the code) so
that same event is not fired again and again as the code reads the other sheet

 

[SPISO]

It worked but I was not shocked that it worked after all your other helps
worked as well.

Let's say I wanted to use the same code but do it for yesterday and two days
ago. I would still want to associate the value next to the dates in "Draw".

Could one change your code from =Date to something like =Date-1 and =Date-2
or something like that? Or would it be better to have yesterday's date in a
cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your
code to associate it with the value in worksheet 'Draw' where the value from
'Draw' can be copied to B4 on the 'Dashboard'?

 

[SPISO]

It worked but I was not shocked that it worked after all your other helps
worked as well.

Let's say I wanted to use the same code but do it for yesterday and two days
ago. I would still want to associate the value next to the dates in "Draw".

Could one change your code from =Date to something like =Date-1 and =Date-2
or something like that? Or would it be better to have yesterday's date in a
cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your
code to associate it with the value in worksheet 'Draw' where the value from
'Draw' can be copied to B4 on the 'Dashboard'?

 

[Sheeloo]

You can use Date - 1 or Date - 2... Excel treats a date as number of days
from 1/1/1900 so all mathematical operations are possible.

You have a programmer's mind...

 

[Sheeloo]

You can use Date - 1 or Date - 2... Excel treats a date as number of days
from 1/1/1900 so all mathematical operations are possible.

You have a programmer's mind...

 

[SPISO]

Thanks again for all your help. I could not have done this without you.

 

[SPISO]

Thanks again for all your help. I could not have done this without you.

 

[SPISO]

I was thinking this weekend about my dashboard that you have helped me
create. Last week you wrote me a code that changed B3 to "" if the cell
associated to today's date = "" in 'draw'. I would like to use that same
blank cell in 'draw' to cause another action when the workbook opens.

I would like cell Q27 in 'Dashboard' to change to the value associated to
today in worksheet 'goals' if there isn't any value associated to today in
'Draw'. Would I just change "" to the sheet name and cell? Is that an easy
modification to the code you have below? What would that look like?

I did a side by side comparison to your two codes where one was caused by a
cell change versus a workbook opening. They do look pretty similar but I am
missing something when i combine the two to get the macro to input a cell
value versus "".

 

[SPISO]

I was thinking this weekend about my dashboard that you have helped me
create. Last week you wrote me a code that changed B3 to "" if the cell
associated to today's date = "" in 'draw'. I would like to use that same
blank cell in 'draw' to cause another action when the workbook opens.

I would like cell Q27 in 'Dashboard' to change to the value associated to
today in worksheet 'goals' if there isn't any value associated to today in
'Draw'. Would I just change "" to the sheet name and cell? Is that an easy
modification to the code you have below? What would that look like?

I did a side by side comparison to your two codes where one was caused by a
cell change versus a workbook opening. They do look pretty similar but I am
missing something when i combine the two to get the macro to input a cell
value versus "".