Start my Macro at the last row of non duplicate data

[playaller]

Hello,

I have a vba script that copies entire rows that contain certain words from sheets(1) and paste the rows onto sheets(2).
Nothing is ever deleted on either sheets so both sheets are updated at the end of the last row.

Being that sheets(1) is the main sheet manually updated on a daily basis, data is never deleted, but I don't want to copy the same information that has already been previously copied and paste to sheet(2) again when I run the macro (which is what happens).

Can someone help with an addition to the code below that will skip all of the previously copied data?

I would suggest that maybe we use the last full row on sheets(2) and find that row on sheets(1) and then have my macro to start one row after that on sheets(1). (Basically the last row on sheets(2) represents the last row previously checked.)

Sub SurveyMove()


Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean
    
    strArray = Array("Error", "Critical Error", "Severe Error")
    
    Set wsSource = Sheets(1)
    
    NoRows = wsSource.Range("A65536").End(xlUp).Row
    
    Set wsDest = Sheets(2)
    DestNoRows = wsDest.Range("A65536").End(xlUp).Row
    For I = 1 To NoRows
    
        Set rngCells = wsSource.Range("A" & I & ":H" & I)
        Found = False
        For J = 0 To UBound(strArray)
            Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
        Next J
        
        If Found Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            
            DestNoRows = DestNoRows + 1
        End If
    Next I
    


    Columns("A:H").EntireColumn.AutoFit
End Sub

Thanks in advance for all of your help.

 

[JoeMo]

Maybe add a static variable that marks the first empty row on Sheets(1) after the macro is run, assuming this is the row where new data will be added before the macro is run again.

Sub SurveyMove()


Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean
Static StartRow As Long
    
    strArray = Array("Error", "Critical Error", "Severe Error")
    
    Set wsSource = Sheets(1)
    
    NoRows = wsSource.Range("A65536").End(xlUp).Row
    
    Set wsDest = Sheets(2)
    DestNoRows = wsDest.Range("A65536").End(xlUp).Row
    For I = StartRow + 1 To NoRows
    
        Set rngCells = wsSource.Range("A" & I & ":H" & I)
        Found = False
        For J = 0 To UBound(strArray)
            Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
        Next J
        
        If Found Then
            rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)
            
            DestNoRows = DestNoRows + 1
        End If
    Next I
    StartRow = NoRows
    Columns("A:H").EntireColumn.AutoFit
End Sub

 

[playaller]

Hello Joe and thank you for your response.

The update you provided works in the 1 instance the workbook is opened but if I close it, open it and rerun it as I would in a day to day operation, your code copy and paste the same duplicate data that my original code does since StartRow is no longer defined on a first run.

So it seems to be a must that I have something to:
1. Find the last row of data in sheets(2) A:H
2. Find (or match) that same row of data in sheets(1) A:H
3. Set StartRow to = the First row after the matched data row

Is there a basic way of accomplishing this?

 

[JoeMo]

Hello Joe and thank you for your response.

The update you provided works in the 1 instance the workbook is opened but if I close it, open it and rerun it as I would in a day to day operation, your code copy and paste the same duplicate data that my original code does since StartRow is no longer defined on a first run.

So it seems to be a must that I have something to:
1. Find the last row of data in sheets(2) A:H
2. Find (or match) that same row of data in sheets(1) A:H
3. Set StartRow to = the First row after the matched data row

Is there a basic way of accomplishing this?

Why not simply store the last row + 1 in some out of the way cell that you can name "StartRow" (Formula>Define Name). Then use For I = Range("StartRow").Value to NoRows

 

[playaller]

Perfect! That was the information I needed to get me where I needed to be. Thanks for all of your help Jo!