Copy/Paste and remove duplicates

[Apple08]

Hi all

I have done a macro to copy data from sheet2 to sheet 3 then remove duplicates, however it doesn't work, please could anyone help?

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Reconcile()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim LastRow AsLong[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    LastRow =Range("A" & Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Sheet2.Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("F2:F" & LastRow).Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Selection.Copy[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Sheets("Reconcile").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("C2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ActiveSheet.Paste[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Application.CutCopyMode = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.Range("$C$2:$C" & LastRow).RemoveDuplicatesColumns:=1, Header:=xlNo[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Range("A2").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[Fluff]

How about

Sub Apple08()
   Dim LastRow As Long
    
   With Sheet2
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("F2:F" & LastRow).Copy
   End With
   With Sheets("Reconcile")
      .Range("C2").PasteSpecial
      Application.CutCopyMode = False
      .Range("$C$2:$C" & LastRow).RemoveDuplicates Columns:=1, Header:=xlNo
   End With
End Sub

 

[Peter_SSs]

If Fluff's suggestion does not work for you, can you expand on this as it provides no detail at all.

.. however it doesn't work,

- Code gives error message? (What message?)
- Wrong result? (samle data and expected result?)
- Excel crashes?
- Code does nothing?
- Something else?
- etc

 

[Apple08]

Thanks Fluff and Peter, it works now! Really appreciated!

 

[Peter_SSs]

Remove Duplicates only works reliably if the data is sorted first. If the data when pasted into column C of 'Reconcile' is not sorted then I suggest that you include a sort of that column in your code before the RemoveDuplicates line.
If you want to see an example of what I am referring to look here.

 

[Apple08]

Hi Peter, could you tell me what the sorting code should be added to my macro to make it more reliable please?

 

[Fluff]

How about

Sub Apple08()
   Dim LastRow As Long
    
   With Sheet2
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("F2:F" & LastRow).Copy
   End With
   With Sheets("Reconcile")
      .Range("C2").PasteSpecial
      Application.CutCopyMode = False
      With .Range("$C$2:$C" & LastRow)
         .Sort key1:=.Resize(1), order1:=xlAscending, Header:=xlNo
         .RemoveDuplicates Columns:=1, Header:=xlNo
      End With
   End With
End Sub

 

[Apple08]

Fab! Thanks Fluff!

 

[Fluff]

My pleasure