How to Update Daily Log In Another Sheet

[Rajesh Kr Joshi]

Hi ,

I have a 2 sheets in a workbook by the name Entry and DataStore. I am entering the daily data in Entry sheet and then manually updating the data in DataStore sheet. Is there a macro to automate this.

Below is the smaple file with expected output comments.
678

Thanks
Rajesh


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

Hi

try this


Option Explicit

Sub kTest()

Dim t, r As Long

t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")
r = Sheet2.Range("a1").CurrentRegion.Rows.Count

If IsError(t) Then
Sheet2.Range("a" & r + 1).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
Sheet2.Range("n" & r + 1).Resize(, 5) = Sheet1.Range("e19:i19").Value
Else
If MsgBox("Entry already exists for date '" & Format(Range("'Entry '!e4").Value, "dd-mmm-yy") & "'" & vbLf & _
"Do you want to overwrite the entry?", vbYesNo) = vbYes Then
Sheet2.Range("a" & t).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
Sheet2.Range("n" & t).Resize(, 5) = Sheet1.Range("e19:i19").Value
End If
End If

End Sub

[Rajesh Kr Joshi]

WOW simply awesome. Working great.
Can i put a condition if someone try to update the data for a date beyond 24 hrs, it will not allow to update the data (Just to prevent data manipulating for older record)

If this is not too much of work for you, else i am fine with the exixting code.

Thanks a TON.
Rajesh

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9xpn-GDkL3o)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg.9zYoeePv8sZ9zYqog9KZ 5B)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdO pm)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rajesh Kr Joshi]

Hi Admin,

If i change the range in the below line from E4 to any other range say D4 the overwrite function doesn’t works. It keeps make duplicate entry. Not sure if I am doing something worng.


t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")

Thanks
Rajesh

[Ingolf]

Cross post How to update daily report in a data base format ? (http://www.mrexcel.com/forum/excel-questions/695045-how-update-daily-report-data-base-format.html)

[Admin]

Hi

try


Option Explicit

Sub kTest()

Dim t, r As Long

t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")
r = Sheet2.Range("a1").CurrentRegion.Rows.Count

If IsError(t) Then
Sheet2.Range("a" & r + 1).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
Sheet2.Range("n" & r + 1).Resize(, 5) = Sheet1.Range("e19:i19").Value
Else
If Date - CDate(Sheet1.Range("e4").Value2) > 0 Then Exit Sub
If MsgBox("Entry already exists for date '" & Format(Range("'Entry '!e4").Value, "dd-mmm-yy") & "'" & vbLf & _
"Do you want to overwrite the entry?", vbYesNo) = vbYes Then
Sheet2.Range("a" & t).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
Sheet2.Range("n" & t).Resize(, 5) = Sheet1.Range("e19:i19").Value
End If
End If

End Sub

as far as changing the range, see the space after 'Entry '!E4. Ensure you spelled it correctly.

[Rajesh Kr Joshi]

Hi Admin,

The space was causing issue, i removed the space and working fine now.
Also the modified code to verify the date is also woking great, only thing i did is added 'Else' after Exit Sub.

Thanks for your help.
Rajesh