Copying data to a new sheet only if data does not exist using a macro

Question

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:

  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 <> "ISRisks") And (ws.Name <> "Closed Risks") And (ws.Name <> "Risk Grading Matrix ") And (ws.Name <> "Sheet1") And (ws.Name <> "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" & 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

Do you want to check if there's *any* data there, or specific data? Also, I ***highly*** suggest reading through, and applying, [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jul 25 '16 at 17:02
Hi @BruceWayne - thank you I'm not that familiar with VBA but will take that into account. Much appreciated — AJCT, Jul 26 '16 at 08:47
You can replace `Range("A3").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy` with this line to do the same thing without using Select - `Range("A3", Cells(Range("A3").End(xlDown).Row, Range("A3").End(xlToRight).Column)).Copy` — tjb1, Jul 26 '16 at 15:07
You can also replace `Sheets("ISRisks").Select ActiveSheet.Cells(LastRow + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False` with this line to prevent selection of the sheet - `Sheets("ISRisks").Cells(LastRow + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False` but I'm not sure how you are updating your last row if you are looping through the sheets... Does this code work if it has to copy more than once? It looks like it will just overwrite the old data. — tjb1, Jul 26 '16 at 15:13
Can you update your original post to include the new code? Also, are you ever storing the value (RISKID) to check against? — tjb1, Jul 26 '16 at 15:30
@tjb1 - I am starting that part now - I wanted to make sure everything else was working okay. I have updated the original code.. — AJCT, Jul 26 '16 at 15:56

score 0 · Answer 1 · answered Jul 25 '16 at 17:08

You can use this code to search a range and determine if the value is there.

With Sheets("Sheet1").Range("A:A")
    Set Rng = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    If Not Rng Is Nothing Then
        'Do This If Found
    Else
        'Do This If Not Found
    End If
End With

You'll need to set the sheet you want to use, the range you want to search and replace FindString with the variable you are checking for. The sections in the if statement are where you can perform actions based on it finding or not finding (paste if not found). That can also be redone to shorten it if you don't need to perform an action if found.

The above example was found here.

Thank you @tjb1 - I'll post my final code when I get it working - I'm very grateful for your help. — AJCT, Jul 26 '16 at 08:49

Copying data to a new sheet only if data does not exist using a macro

1 Answers1