Macro won't skip cells already copied

[Rymare]

I have a macro that runs through my source sheet "CleanData" and copies any row in that sheet to the active sheet provided the active sheet's name is in column A. It skips over rows that do not meet this criteria. So far, so good. The problem I'm having is that when you run the macro again it does not recheck the active sheet to see if the same row is already recorded. So it gives me duplicates.
I'm using fake data as the actual data is protected info, but needless to say the list under the Inspector heading is extremely long which is why I'm trying to avoid fixed ranges(like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">"A5:A32"</code> etc) and fixed criteria (having the macro say things like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">rngcel.value = "Harry"</code> for example would cause problems because what if a new inspector gets added on and his name is not Harry? Someone has to go in and change the macro each time a new inspector is added. Instead I use things like <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">rngcel.value = Activesheet.name</code> since each inspector gets his/her own sheet).
Here's the flow
You run the macro from the inspectors sheet, in this example, the Sheet named Harry. Harry comes in and runs the macro to see what new work he needs to do:

So far so good:
Well, what if Harry comes in one day and wants to see if Voldemort has given him new work? He needs to update his sheet again to check right?
So he runs the macro again, but now the same work orders are getting copied in with the new ones, and he doesn't need the same info twice:

Here's the code which I cobbled together--even from here I think. If you recognize it please by all means, I'm happy to put your name in it.

Sub CopyColumns()


Dim wsSource As Worksheet
Dim wsDestin As Worksheet
Dim lngDestinRow As Long
Dim rngSource As Range
Dim rngCel As Range




Set wsSource = Sheets("CleanData")     'Edit "Sheet1" to your source sheet name
Set wsDestin = ActiveSheet




With wsSource
    'Following line assumes column headers in Source worksheet so starts at row2
    Set rngSource = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))


End With


For Each rngCel In rngSource
    If rngCel.Value = ActiveSheet.name Then
        With wsDestin
            'Following line assumes column headers in Destination worksheet
            lngDestinRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
            rngCel.EntireRow.copy Destination:=wsDestin.Cells(lngDestinRow, "A")
        End With
    End If
Next rngCel


End Sub

I tried to filter out the duplicates using:

And IsError(Application.Match(rngCel.Value, rowname, 0))

in this line of code:

For Each rngCel In rngSource
        If rngCel.Value = ActiveSheet.name Then

And I tried to define rowname as a range of all previous rows in the sheet. It did not work.


I initially had formulas within each sheets' cells that gathered all this data but it severely slowed down an already bogged down excel file.

I then tried this

   With wsDestin        
Set destrng = .Range(.Cells(2, "G"), .Cells(.Rows.Count, "G").End(x1Up))
    End With
   
    
    For Each rngCel In rngSource
        If rngCel.Value = ActiveSheet.name And rngCel.Value <> destrng Then

It also didn't work. The W.O. Number is a unique value in Harry's sheet.

I also tried posting this earlier and it didn't post--at least I didn't see it and got no notifications and couldn't find it in my subscriptions. I apologize if I did something wrong. I'm at my wits end.

 

[Fluff]

Duplicated https://www.mrexcel.com/forum/excel...el-vba-skipping-copied-cells.html#post5054174

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.