Macro modification to remove the update the latest value

[PrashanthGM12345]

Hi i have the excel file uploaded for yourr reference.
I am doing the following activity.
A user1 clicks on "Insert sheet" button in Introduction sheet and new sheet gets created with name "CSI_1"
He fills all the details with respect to "CSI_1" like completion % as 10%, status as "proposed" and clicks on "Update Dashboard", all the details are updated in the dashboard sheet.

Now user2 clicks on "Insert sheet" button in Introduction sheet and new sheet gets created with name "CSI_2"
He fills all the details with respect to "CSI_2" ike completion % as 20%, status as "Work in progress" and clicks on "Update Dashboard", all the details are updated in the dashboard sheet.

Again User1 goes to the CSI_1 sheet and updates the % of completion as 100% and status as "completed" and the above code which i have written updates a new row in the dashboard sheet. Now i have 3 rows like this in the dashboard table

CSI_1 | Proposed | 10
CSI_2 | Work in progress | 20
CSI_1 | Completed | 100

but i want it with the updated value for CSI_1 like this which is displayed below instead of having 2 entries for CSI_1

CSI_1 |Completed | 100
CSI_2 | Work in progress | 20

Can anyone guide how to achieve this following scenario

 

[Ajesh]

Why not check the Dashboard before updating if it already has entry for CSI_1. If yes, update the values in that row or else insert new entry.

Thanks/Ajesh

 

[PrashanthGM12345]

Why not check the Dashboard before updating if it already has entry for CSI_1. If yes, update the values in that row or else insert new entry.

Thanks/Ajesh

Can you help me with the vba code for this?

 

[Ajesh]

Prashanth, please can you explain why are you checking if cell G14 is blank in your CSI sheets in update dashboard sub? This cell will never be blank if I am not wrong.

thanks/Ajesh

 

[PrashanthGM12345]

Prashanth, please can you explain why are you checking if cell G14 is blank in your CSI sheets in update dashboard sub? This cell will never be blank if I am not wrong.

thanks/Ajesh

oh my bad. i have given wrong cell reference due to typo. it should have been G7 instead of G14.

 

[Ajesh]

Try this:

Sub Update_Dashboard()
Dim i1 As String
Dim i2 As String
Dim i3 As String
Dim c As Range
Dim lRow, UpdateRow As Long
Dim dbSht As Worksheet

If ActiveSheet.Cells(7, "G").Value <> "" Then
    Set dbSht = ThisWorkbook.Worksheets("Dashboard")
    i1 = ActiveSheet.Cells(7, "G").Value
    i2 = ActiveSheet.Cells(11, "G").Value
    i3 = ActiveSheet.Cells(14, "K").Value
    With dbSht
        lRow = .Cells(.Rows.Count, 6).End(xlUp).Row
        UpdateRow = lRow + 1
        For Each c In .Range("F24:F" & lRow)
            If c.Value = i1 Then
                UpdateRow = c.Row
               Exit For
            End If
        Next c
        .Cells(UpdateRow, 6).Value = i1
        .Cells(UpdateRow, 8).Value = i2
        .Cells(UpdateRow, 11).Value = i3
    End With
Else
    MsgBox "Please ensure CSI Reference ID is not blank."
End If

End Sub

 

[PrashanthGM12345]

Try this:

Sub Update_Dashboard()
Dim i1 As String
Dim i2 As String
Dim i3 As String
Dim c As Range
Dim lRow, UpdateRow As Long
Dim dbSht As Worksheet

If ActiveSheet.Cells(7, "G").Value <> "" Then
    Set dbSht = ThisWorkbook.Worksheets("Dashboard")
    i1 = ActiveSheet.Cells(7, "G").Value
    i2 = ActiveSheet.Cells(11, "G").Value
    i3 = ActiveSheet.Cells(14, "K").Value
    With dbSht
        lRow = .Cells(.Rows.Count, 6).End(xlUp).Row
        UpdateRow = lRow + 1
        For Each c In .Range("F24:F" & lRow)
            If c.Value = i1 Then
                UpdateRow = c.Row
               Exit For
            End If
        Next c
        .Cells(UpdateRow, 6).Value = i1
        .Cells(UpdateRow, 8).Value = i2
        .Cells(UpdateRow, 11).Value = i3
    End With
Else
    MsgBox "Please ensure CSI Reference ID is not blank."
End If

End Sub

Awesome. Works Perfectly.
Excellent work Ajesh. Highly appreciate your effort.

 

[Ajesh]

Happy to help where I can.

thanks