Check if value already exists and if does skip it, don't copy cells

    Hi all,


    Please, i need help if someone can help me.


    I have two sheets "Recruitment" and "Azuriranje". In sheet Recruitment in column A i have values that should be checked, and if exist in sheet Azuriranje in column I16:I40 it should skip copy/paste part in vba, it should do nothing. If values doesn't exist in Azuriranje, copy/paste part from vba pasted here should be executed.


    Here is vba:

    Code
    Sub Azuriranje_AddPolaznik()Application.ScreenUpdating = FalseSheets("Recruitment").UnprotectSheets("Azuriranje").UnprotectSheets("Polaznici").UnprotectSheets("Treninzi").Unprotect    Dim LastRow As Long    Dim LastRow1 As Long    LastRow = Sheets("Recruitment").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    LastRow1 = Sheets("Azuriranje").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row    Dim y As Long    y = Sheets("Azuriranje").Range("I16:I" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row    Dim val1 As Range    Set foundVal1 = Sheets("Recruitment").Range("AP:AP").Find(Sheets("Azuriranje").Range("E16"), LookIn:=xlValues, lookat:=xlWhole)    If Not foundVal1 Is Nothing Then        Sheets("Recruitment").Activate        Range("A17:Bi" & LastRow).Select        Selection.AutoFilter                Sheets("Recruitment").Range("A17:Bi" & LastRow).AutoFilter Field:=42, Criteria1:=Sheets("Azuriranje").Range("E16").Value        Sheets("Recruitment").Range("A18:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "I").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("C18:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "J").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("D18:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "K").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("e18:e" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "o").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("i18:i" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "s").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("j18:j" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "t").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("o18:o" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "u").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("n18:n" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "v").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("g18:g" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "w").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("h18:h" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "x").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("k18:k" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "y").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("l18:l" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "z").PasteSpecial xlPasteValues        Sheets("Recruitment").Range("m18:m" & LastRow).SpecialCells(xlCellTypeVisible).Copy        Sheets("Azuriranje").Cells(y, "aa").PasteSpecial xlPasteValues    Sheets("Azuriranje").Activate        Sheets("Azuriranje").Range("E18").Select        MsgBox "Podaci uspješno uneseni iz Recruitment tablice.", _            vbOKOnly        If Sheets("Recruitment").FilterMode Then Sheets("Recruitment").ShowAllData    Else        MsgBox "Ne postoji poveznica. Vjerojatno nisu upisane šifre treninga polaznicima u Recruitment tablici!" & vbNewLine & vbNewLine & "Provjerite jesu li upisane šifre treninga polaznicima u Recruitment tablici!", vbOKOnly, "Ne postoji šifra"        Sheets("Azuriranje").Range("E16").ClearContents        Sheets("Azuriranje").Range("E16").Select        Exit Sub    End IfSheets("Recruitment").Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueSheets("Azuriranje").Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueSheets("Polaznici").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=TrueSheets("Treninzi").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=TrueApplication.CutCopyMode = FalseMsgBox "Podaci uspješno uneseni iz Recruitment tablice.", _            vbOKOnlyEnd Sub[/COLOR][COLOR=#333333]


    [/COLOR]
    I would be greatfull if someone could help me.


    Thank you in advance,


    Kind regards,
    Matija

    Re: Check if value already exists and if does skip it, don't copy cells


    Do you have a sample file that I can work on?

    Re: Check if value already exists and if does skip it, don't copy cells


    Sorry, i have it, but it has like million personal information in it. Is it possible for you to help me without sample file? If not, i'll give my best to clear all personal informations from it.


    Thnx,
    Matija

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login