Copy data to another sheet and ask to overwrite if record already exists

[nuficek]

Hi,
I have a form where I'm able to save, search or delete data which are stored on the another sheet. When I store data (based on unique ID) to another sheet it always stores data to the last available row. But I don't know how to check if the record already exists and ask to overwrite it or not.
I have first sheet named "Form" where is unique ID composed from B3 and D3. Then there is many random fields in the form. All fields are stored on the another sheet named "Data Storage" always in the single row starting row 5 as there is a header. So it starts B5 (which matches B3 from sheet "Form") and C5 (which matches D3 from sheet "Form") and then it continuous with the rest fields. So one row is one record with unique IDs B5 and C5.

Option Explicit



Public Sub saveForm()

Dim NextRow As Long, Ws As Worksheet

    

    Set Ws = Sheets("Data Storage")

    NextRow = Ws.Cells(Rows.Count, "B").End(xlUp).Row + 1

    Application.ScreenUpdating = False

    

        With Sheets("Form")

        

'       UNIQE ID

        Ws.Range("B" & NextRow).Value = .Range("B3").Value

        Ws.Range("C" & NextRow).Value = .Range("D3").Value

        

'                                  DATA #1

        Ws.Range("D" & NextRow).Value = .Range("B8").Value

        Ws.Range("E" & NextRow).Value = .Range("C8").Value

        Ws.Range("F" & NextRow).Value = .Range("D8").Value

        Ws.Range("G" & NextRow).Value = .Range("E8").Value

        Ws.Range("H" & NextRow).Value = .Range("F8").Value

        Ws.Range("I" & NextRow).Value = .Range("H8").Value

        Ws.Range("J" & NextRow).Value = .Range("I8").Value

        Ws.Range("K" & NextRow).Value = .Range("J8").Value

                

'                                  DATA#2

        Ws.Range("L" & NextRow).Value = .Range("B9").Value

        Ws.Range("M" & NextRow).Value = .Range("C9").Value

        Ws.Range("N" & NextRow).Value = .Range("D9").Value

        Ws.Range("O" & NextRow).Value = .Range("E9").Value

        Ws.Range("P" & NextRow).Value = .Range("F9").Value

        Ws.Range("Q" & NextRow).Value = .Range("H9").Value

        Ws.Range("R" & NextRow).Value = .Range("I9").Value

        Ws.Range("S" & NextRow).Value = .Range("J9").Value



'                                   TOTAL

        Ws.Range("T" & NextRow).Value = .Range("I14").Value

        

'                                   NOTES

        Ws.Range("U" & NextRow).Value = .Range("C27").Value

        

'                                   SIGNATURE DATE

        Ws.Range("V" & NextRow).Value = .Range("C34").Value

        

              

'                                                    .

'                                                    .

'                                                   etc.



End With

    

    Application.ScreenUpdating = True

    MsgBox "Form no. " & Sheets("Form").Range("B3").Value & " " & Sheets("Form").Range("D3").Value & " successfully saved!"

    

End Sub

I would really appreciate help as I'm still new in VBA and doing it just for fun for myself.

 

[dmt32]

Nope, I totally missed these two. I'm sorry, my mistake. It works now!

easily done - glad resolved & appreciate feedback

Dave