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.
Daily Data Store.xlsx

Thanks
Rajesh


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
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=Ugz_p0kVGrLntPtYzCt4AaABAg
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.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=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=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=312724#p312724
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?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
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
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 ?

[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