how to copy duplicate cells to a second sheet?

[AccountingJ]

Hi everyone, I have sheets that sometimes has up to 5k rows. Column E is known to sometimes have duplicates so I have the macro below to look and copy those duplicate cells to sheet2. When I run it I get "No cells were found." and i'm not sure why.

Option Explicit

Sub FilterAndCopy()

Dim wstSource As Worksheet, _
    wstOutput As Worksheet
Dim rngMyData As Range, _
    helperRng As Range

Set wstSource = Worksheets("Sheet1")
Set wstOutput = Worksheets("Sheet2")

Application.ScreenUpdating = False

With wstSource
    Set rngMyData = .Range("A1:R" & .Range("E" & .Rows.Count).End(xlUp).Row)
End With
Set helperRng = rngMyData.Offset(, rngMyData.Columns.Count + 1).Resize(, 1)

With helperRng
    .FormulaR1C1 = "=if(countif(C1,RC1)>1,"""",1)"
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Copy Destination:=wstOutput.Cells(1, 1)
    .ClearContents
End With

Application.ScreenUpdating = True

End Sub

[Sintek]

That's because there are no blanks...

[AccountingJ]

sheet1 has values in its cells.

[Sintek]

Yes, but you are trying to copy rows which have blank cells in Col T and there aren't any...
Put an if statement to check blankcell count first or make use of On error resume Next | On error goto 0

[AccountingJ]

Where are you getting col T? it should be E.

[Sintek]

Your formula is placed in Col T (Helper Range) and then you are checking for blanks in thiscolumn to copy over to other sheet?
If all rows are 1 then there are no duplicates...If there are blanks in Col T then there are duplicates to copy over...

If Evaluate("=COUNTBLANK(" & .Address & ")") > 0 Then .SpecialCells(xlCellTypeBlanks).EntireRow.Copy Destination:=wstOutput.Cells(1, 1)

Put a duplicate in Col A and run your code...It will not error because it will find 2 blank cells in Col T
Which is the actual Col to check for Dups...Your formula is using col A

[AccountingJ]

got it, thank you, I changed it to this:

.SpecialCells(xlCellTypeBlanks).EntireRow.Copy Destination:=wstOutput.Cells(5, 1)

it works, except it does not copy to the first available row (row 2 is the first available one).

[Sintek]

Not sure what got it means...Is the sample data and setup still same?...First row is Header?

See upload...It will copy highlighted rows...

[AccountingJ]

Yes same set-up, first row is header but I managed to make the change in your macro so it can start copying on the second row of sheet2. If I was even a fraction as smart as you are when it comes to macros, then my life would be simpler.

Thanks again, i really appreciate it.

[Sintek]

can start copying on the second row of sheet2

Oh man ... now I get it...you meant copy to...not copy from...

Your code was copying to first row...

wstOutput.Cells(1, 1)

[AccountingJ]

No worries, I didn't explain correctly the first time. Thanks Sintek.