Copy cells to another worksheet if it does not exist

Question

Copy cells to another worksheet if it does not exist

Navigation

#1 by (0 votes)

0

In the "BASE" worksheet, enter various customer data (NIF, name, address, telephone, email). I have a "DATA" worksheet where you have a list of multiple clients.

I need to create a button in the "BASE" worksheet that copies the client data to the "DATA" worksheet if it does not exist based on the NIF but if it exists it updates the information in the "DATA" worksheet.

I also need to create a button in the "BASE" worksheet that searches the WS "DATA" for a certain "NIF" and copies the information to the WS "BASE".

I hope you can help me. I do not have much experience with vba and this is going to save me a lot of work.

Thank you

excel vba

asked by anonymous 30.03.2017 / 13:18

1 answer

When I refer to more than one column using FormulaR1C1 Comparison of Javascript strings

score 0 · Answer 1

Come on. If I understood correctly, would that NIF be the index of the spreadsheet? In this case the following code should help you:

Sub copiar()

lRow1 = Plan1.Cells(Rows.Count, 1).End(xlUp).Row
lRow2 = Plan2.Cells(Rows.Count, 1).End(xlUp).Row
lCol1 = Plan1.Cells(1, Columns.Count).End(xlToLeft).Column
lCol2 = Plan2.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To lRow1
    x = 0
    For d = 2 To lRow2

        If Plan1.Cells(i, 1).Value = Plan2.Cells(d, 1).Value Then

            For b = 1 To lCol1

                Plan2.Cells(d, b).Value = Plan1.Cells(i, b).Value

            Next b
            x = x + 1
            Exit For

        End If

    Next d

    If x = 0 Then
        lRow2 = lRow2 + 1
        For b = 1 To lCol1

            Plan2.Cells(lRow2, b).Value = Plan1.Cells(i, b).Value

        Next b
    End If


Next i


End Sub

This code will verify that what is in the i line of Plan1 (BASE) exists in Plan2 (DATA) checking if the NIF already exists, if it does not exist it copies and repeats the process for the next line until finishing the filled lines in Plan1 (DATA).