Copying data to a new sheet only if data does not exist using a macro
<p>I have created a simple macro to filter data and copy and add it to a specific sheet [ISRisks] after the last row. I realize however that I am not sure how to check to see if the data already exists in the [ISRisks] sheet! Please could someone help - column a in [ISRisks] contains the unique RISKID field to check.
This is what I have so far:</p>

<pre><code>  Sub ISRISKCOPY()
        '
        Dim lMaxRows  As Long
        Dim ws As Worksheet
        Dim rng As Range
        Dim LR As Long
        Dim rTable As Range

     For Each ws In ActiveWorkbook.Worksheets
                If (ws.Name &lt;&gt; "ISRisks") And (ws.Name &lt;&gt; "Closed Risks") And (ws.Name &lt;&gt; "Risk Grading Matrix ") And (ws.Name &lt;&gt; "Sheet1") And (ws.Name &lt;&gt; "Sheet2") Then
            Application.DisplayAlerts = False

            With ws

             If .FilterMode Then .AutoFilterMode = False
            Sheets(ws.Name).Range("$A$2:$W$2").AutoFilter Field:=5, Criteria1:=Array("IS", "IS - Information Security"), Operator:=xlFilterValues
            Set rTable = Sheets(ws.Name).AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible)
            rTable.Copy
            lMaxRows = Sheets("ISRisks").Cells(Rows.Count, "a").End(xlUp).Row

             Range("a" &amp; lMaxRows + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

            End With

            Else
                End If

            Next ws

            'sorting unique id column
            Sheets("ISRisks").Range("A1").Select
            ActiveWorkbook.Worksheets("ISRisks").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("ISRisks").Sort.SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("ISRisks").Sort
            .SetRange Range("A2:A10000")
            .Header = xlNo

.MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Application.DisplayAlerts = True
    End Sub
</code></pre>