Excel 365 Compare Date & Copy Cell Data for Data Base

[bz61]

Hello, I'm not a proficient at Excel VBA programming & in need of assistance with the following issue: I have an excel workbook with multiple sheets for planning and building a data base. Attached is one of the sheets that extracts data from one of the other sheets. The upper left range of cells tracks the data live, so I'm unable to use an excel program to transfer the data to the right-side columns and save it. I've attached a VBA program that I found on this site and attempted to modify without much success. It will only copy 3 of the cells after that I get errors if I try to add more lines of code. I need to have the data on the left side under the "AW ENG COUNT" column to be transferred over to the corresponding columns and date on the right side. I'm using the "Today ()" function on the left side "Date" column and Excel auto populate date function on the right-side column. I will be putting the code into the Private Sub Workbook_Open & Close, so when Planners open or close this Workbook, the data will be updated to the last entry for that day and be repeated daily. Thank you in advance.

Private Sub Workbook_Close()

Dim Cl As range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Graph Data")
        For Each Cl In .range("d5", .range("d" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(1, -1).Value
        Next Cl
    End With
    With Sheets("Graph Data")
        For Each Cl In .range("ba5", .range("ba" & Rows.Count).End(xlUp))
            If Dic.exists(Cl.Value) Then Cl.Offset(, -4).Value = Dic(Cl.Value)
        Next Cl
    End With
    
End Sub

 

[bz61]

Ok, I tried the "Open Workbook" code and that worked perfectly. Each value when change updated the correct data and column. I would like to paste this code to the "Close Workbook" instead of the "Open Workbook" that way the daily changes will be recorded to the correct date and not the following day. Would the VBA code for the same as the Open?

I would like to thank you for being so patient with me and in resolving this issue. I was able to modify the code so it will update prior to closing. Do you have a favorite charity that I could donate to in your name?

 

[skillilea]

Here you go.

So are you good?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RangeInsertOrUpdateBasedOnToday
End Sub

 

[bz61]

Here you go.

So are you good?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RangeInsertOrUpdateBasedOnToday
End Sub

Yes, thank you. Do you have a favorite charity????

 

[skillilea]

Haha , We have tons of them. But, no worries. You just keep tearing it up

Glad to help!

Welcome to the board!!!