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]

show me the two sheets and the code behind. I didn't know there were 2 sheets. Thought it was all on one based on the picture above.

 

[bz61]

Ok, the Planners populate the "Planning Template" sheet and at the bottom of many columns are totalizers (sum, countif, countifs, etc...), but only a few of the totals are used for the graphs and those are showing up in the "Graph Data" Sheet by using the (='Planning Template'! Cell # here). I've attached a pic of one of the cells on the "Graph Data" sheet, so you can see the formula. I use this for the bar graph which is live and the plan was to populate the data base from this daily automatically.

 

[skillilea]

OK remove all the code from your graph data sheet.

Add the below follow the comments on each sub

'  add this to your Planning template data sheet
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    
    RangeInsertOrUpdateBasedOnToday
End Sub


' add this to a new module
' right click on modules in the VBE editor and add this sub
Public Sub RangeInsertOrUpdateBasedOnToday()
    
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim ar As Variant
    Dim dT As Date
    Dim rng As Range
    Dim theRow As ListRow

    dT = Date
    Set ws = ThisWorkbook.Sheets("Graph Data") '  your sheet name
    Set tbl = ws.ListObjects("tblTrack")  ' your table name

    ' get the values to update or insert
    ar = Range("C5:C9").Value
    
    If tbl.DataBodyRange Is Nothing Then GoTo insert
    Set rng = tbl.ListColumns("Date").DataBodyRange.Find(What:=dT, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then GoTo insert
    
    ' update
    rng.Value = dT
    Set rng = rng.Offset(, 1).Resize(, 5)
    rng.Value = Application.Transpose(ar)
    
    GoTo out
       
update:
        Debug.Print "update"
    GoTo out
    
insert:
    Set theRow = tbl.ListRows.Add ' add at bottom of table
    theRow.Range.Cells(, 1).Value = dT
    Set rng = theRow.Range.Cells(, 2)
    Set rng = rng.Resize(, 5)
    rng.Value = Application.Transpose(ar)

out:
    Set theRow = Nothing
    Set tbl = Nothing
    Set ws = Nothing
End Sub

 

[bz61]

OK remove all the code from your graph data sheet.

Add the below follow the comments on each sub

'  add this to your Planning template data sheet
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
   
    RangeInsertOrUpdateBasedOnToday
End Sub


' add this to a new module
' right click on modules in the VBE editor and add this sub
Public Sub RangeInsertOrUpdateBasedOnToday()
   
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim ar As Variant
    Dim dT As Date
    Dim rng As Range
    Dim theRow As ListRow

    dT = Date
    Set ws = ThisWorkbook.Sheets("Graph Data") '  your sheet name
    Set tbl = ws.ListObjects("tblTrack")  ' your table name

    ' get the values to update or insert
    ar = Range("C5:C9").Value
   
    If tbl.DataBodyRange Is Nothing Then GoTo insert
    Set rng = tbl.ListColumns("Date").DataBodyRange.Find(What:=dT, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then GoTo insert
   
    ' update
    rng.Value = dT
    Set rng = rng.Offset(, 1).Resize(, 5)
    rng.Value = Application.Transpose(ar)
   
    GoTo out
      
update:
        Debug.Print "update"
    GoTo out
   
insert:
    Set theRow = tbl.ListRows.Add ' add at bottom of table
    theRow.Range.Cells(, 1).Value = dT
    Set rng = theRow.Range.Cells(, 2)
    Set rng = rng.Resize(, 5)
    rng.Value = Application.Transpose(ar)

out:
    Set theRow = Nothing
    Set tbl = Nothing
    Set ws = Nothing
End Sub

Ok, I believe you're on the correct path. The first VBA code would only copy the C5:C9 range values; if they were numbers manually inputted, but now the new VBA code copies the values in the C5:C9 range that are populated from the Planning Template using the (=) equation. The issue that is common to both VBA codes is that it will not run either code unless prompted by me in the VBA editor using the "Run Sub/UserForm" (F5) then when the Macro name screen appears I have to select the Sub Macro name "RangeInsertOrUpdateBasedOnToday" to execute. I've attached pics of what I'm seeing and the code that was entered per the directions given.

 

[bz61]

Ok, I believe you're on the correct path. The first VBA code would only copy the C5:C9 range values; if they were numbers manually inputted, but now the new VBA code copies the values in the C5:C9 range that are populated from the Planning Template using the (=) equation. The issue that is common to both VBA codes is that it will not run either code unless prompted by me in the VBA editor using the "Run Sub/UserForm" (F5) then when the Macro name screen appears I have to select the Sub Macro name "RangeInsertOrUpdateBasedOnToday" to execute. I've attached pics of what I'm seeing and the code that was entered per the directions given.

Is it allowed per the Forum guidelines to verbally communicate on such issues?

 

[skillilea]

Couple things. Nope, the board does not condone handing out personal info and/or private conversations around threads. Not to worry:

This code will fire anytime the Planning Template changes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    
    RangeInsertOrUpdateBasedOnToday
End Sub

change this line in RangeInsertOrUpdate

    ' get the values to update or insert
    ar = ws.Range("C5:C9").Value

 

[skillilea]

Another Thought. If the values aren't changing on the Planning Template and you only want to track whatever is there on the day.
Add this to your workbook on open event "ThisWorkbook" tab

Private Sub Workbook_Open()
    RangeInsertOrUpdateBasedOnToday
End Sub

 

[bz61]

Couple things. Nope, the board does not condone handing out personal info and/or private conversations around threads. Not to worry:

This code will fire anytime the Planning Template changes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
   
    RangeInsertOrUpdateBasedOnToday
End Sub

change this line in RangeInsertOrUpdate

    ' get the values to update or insert
    ar = ws.Range("C5:C9").Value

Ok, that did the trick for 4 of the 5 points in the range. But when the C5 value is changed on the "Planning Template" sheet the "Graph Data" sheet doesn't update the data base table. I've attached pic's to show what is happening now... sorry.

 

[bz61]

Ok, that did the trick for 4 of the 5 points in the range. But when the C5 value is changed on the "Planning Template" sheet the "Graph Data" sheet doesn't update the data base table. I've attached pic's to show what is happening now... sorry.

That point uses a "Countif "function to calculate the value on the "Planning Sheet".

 

[bz61]

That point uses a "Countif "function to calculate the value on the "Planning Sheet".

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?