Paste isn't working correctly and sometimes pastes duplicate data

Question

I am writing a Macro to filter, copy and paste some data from one sheet to another in Excel. The data contains 3 categories by which I need to filter, and I am successfully able to do that. However when I copy and paste the data onto another sheet, it sometimes duplicates the pasted data.

For Example: Output should look like:

 Record1

 Record2

But it actually looks like this:

 Record1

 Record2

 Record1

 Record2

I am using

 ActiveSheet.AutoFilter.Range.Copy

for copying and

 ActiveSheet.Paste

for pasting.

I have tried clearing the clipboard for every new copy paste and have checked my code to make sure I'm not pasting the same thing multiple times, and I have no clue as to why this is happening.

This is what my code looks like:

Clearing the clipboard, filters and temp sheets for reuse:
Application.CutCopyMode = False
Sheets("Sheet2").AutoFilterMode = False
Sheets("Sheet3").AutoFilterMode = False
Sheets("Sheet3").Cells.Clear

'Filtering out the Category3 records:
        Sheets("Sheet2").Activate

            'Getting the range of the sheet
            With ActiveSheet
                Dim LR25 As Long
                    LR25 = .Cells(.Rows.Count, "A").End(xlUp).Row
            End With

            ActiveSheet.AutoFilterMode = False
            ActiveSheet.Range("$A$1:$X$" & LR25).AutoFilter Field:=2, Criteria1:="Category3"

            'Copying the filtered data:
                ActiveSheet.AutoFilter.Range.Copy

                'Pasting the copied data:
                    Sheets("Sheet3").Activate
                    ActiveSheet.Paste

                'Clearing the clipboard:
                Application.CutCopyMode = False

                'Getting the range of the sheet for dynamic range adjustment
                 With ActiveSheet
                    Dim LR26 As Long
                        LR26 = .Cells(.Rows.Count, "A").End(xlUp).Row
                End With

                        Sheets("Sheet5").Activate
                        With ActiveSheet
                                Range("C64").Value = LR26 - 1
                        End With

                'Filtering  records :
                Sheets("Sheet3").Activate


                                ActiveSheet.Range("$A$1:$X$" & LR26).AutoFilter Field:=5, Criteria1:="Filter-criteria"

                            'Deleting all the records for getting the desired records:
                                ActiveSheet.Range("$A$1:$X$" & LR26).Offset(1).SpecialCells(12).EntireRow.Delete
                                ActiveSheet.AutoFilterMode = False


                'Puting the results in the desired sheet
                Sheets("Sheet5").Activate
                With ActiveSheet

                        Range("D64").Formula = "=COUNTIF('Sheet3'!B:B,""Category3"")"
                        Range("D64").Value = Range("D64").Value


                        Range("E64").Formula = "=COUNTIF('Sheet3'!T:T,""Example Category"")"
                        Range("E64").Value = Range("E64").Value


                        Range("F64").Formula = "=COUNTIF('Sheet3'!T:T,""Example Catgeory"")"
                        Range("F64").Value = Range("F64").Value


                        Range("G64").Formula = "=COUNTIF('Sheet3'!T:T,""Example Catgeory"")"
                        Range("G64").Value = Range("G64").Value
                End With
End Sub

And here is another piece of information, which I'm not sure if it will help or not:

Category 1 contains 10 records
Category 2 conatins 20 records 
Catgeory 3 contains 5 records

But the Output shows :

 Category 1 contains 10 records
 Category 2 conatins 20 records 
 Catgeory 3 contains 20 records

You are going to need to show more of your code for us to see the problem. Those two lines are not enough. — Scott Craner, May 21 '18 at 13:15

score 2 · Answer 1 · answered May 21 '18 at 13:29

You're using:

ActiveSheet.AutoFilter.Range.Copy

This method will Copy the entire range of the auto-filtered table into the buffer. When you paste, it can ignore if some of that range was visible at the time or not.

If you only copy the visible cells - you just get those you want:

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy

Please note though, you should try to avoid using ActiveSheet, Activate, Select etc. and instead directly reference the ranges involved. Here is a link that is a really good reference guide for this approach..

Tried it, but unfortunately it didn't change anything. I will look into the Avoiding ActiveSheet and etc topics. I do remember seeing a post about that. Cheers! — K.EL, May 21 '18 at 13:33

Paste isn't working correctly and sometimes pastes duplicate data

1 Answers1