Help with VBA Code.

[avalite]

Hi,
So i am new to vba and need a little help with a vba coding problem. My code is copying data from one workbook"Raw customer data" to second workbook "Customer Data-Base". I need to modify this code so that if the customer ID from " raw customer data" workbook present in cells A2, A21 and A39 already exists in the second workbook "customer data-base" column A, the user is prompted "are you sure" before over-writing the data in the cells. If the customer ID is new and does not exist then the code should paste the data in the last available row. Here is the code that i have written:

Sub Copy_Dbase_Code_msgbox()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lDestLastRow As Long
Dim Samlastrow As Long
Dim MsgConfirm As VBA.VbMsgBoxResult


Application.ScreenUpdating = False


Workbooks.Open Filename:="C:\Users\Muhammad Ali\Desktop\Customer Data.xlsx"
Set wsCopy = Workbooks("Customer ID.xlsm").Worksheets("customers")
Set wsDest = Workbooks("Customer Data.xlsx").Worksheets("Data")

Samlastrow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

Let c = wsCopy.Cells(10, 1).Value

For r = 1 To lDestLastRow

If r = c Then

MsgConfirm = MsgBox("This data already exists, Are you sure that you want to overwrite." _
& vbNewLine & "Would you like to continue?", vbOKCancel + vbDefaultButton2, "Confirmation Required")
If MsgConfirm = vbCancel Then Exit Sub

Else
wsCopy.Range("A2").Copy
wsDest.Range("A" & lDestLastRow).PasteSpecial xlPasteValues, Transpose:=True
wsCopy.Range("B3:B18").Copy
wsDest.Range("B" & lDestLastRow).PasteSpecial xlPasteValues, Transpose:=True

wsCopy.Range("A21").Copy
wsDest.Range("A" & lDestLastRow + 1).PasteSpecial xlPasteValues, Transpose:=True
wsCopy.Range("B22:B37").Copy
wsDest.Range("B" & lDestLastRow + 1).PasteSpecial xlPasteValues, Transpose:=True


wsCopy.Range("A39").Copy
wsDest.Range("A" & lDestLastRow + 2).PasteSpecial xlPasteValues, Transpose:=True
wsCopy.Range("B40:B55").Copy
wsDest.Range("B" & lDestLastRow + 2).PasteSpecial xlPasteValues, Transpose:=True
End If
Next r
VBA.Interaction.MsgBox ("Data copied to database"), vbOKOnly, "Done!"

Application.ScreenUpdating = True

End Sub

Can someone help me with that. Thanks

 

[hernantorres23]

Welcome. In addition to your code, please try prepare a data example using add-in specifically for this, it can be found here XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square