Remove Duplicate Macro

[DRExcel515]

I have a spreadsheet that lists a set of identifiers (142182) in column "F" that every month I copy and paste into another tab labled "DISTINCT", which I then do the remove duplicates process. Is there a way to use a macro to say from these four tabs (CRO ICO SICEHY and SICCRO) take column F (disregarding the header in F1) and paste only non-duplicate values into these four tabs (CROMV ICOMV SICEHYMV and SICCROMV) in column A (starting in A2 on the tabs as to leave the header in tact)?

<tbody>
</tbody>

Thank you in advance for any advice/help!

 

[Fluff]

Re: Remove Duplicate Macro Help

How about

Sub CopyPasteDuplicates()

   Dim Shts As String
   Dim Ary As Variant
   Dim Cnt As Long
   Dim Cl As Range
   Dim Dic As Object
   
   Shts = "CRO|CROMV|ICO|ICOMV|SICEHY|SICEHYMV|SICCRO|SICCROMV"
   Ary = Split(Shts, "|")
   
   Set Dic = CreateObject("scripting.dictionary")
      For Cnt = LBound(Ary) To UBound(Ary) Step 2
         With Sheets(Ary(Cnt))
            For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
               If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Nothing
            Next Cl
            Sheets(Ary(Cnt + 1)).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Dic.Count).Value = _
               Application.Transpose(Dic.keys)
            Dic.removeall
         End With
      Next Cnt

End Sub

 

[DRExcel515]

Re: Remove Duplicate Macro Help

Thank you so much! This works perfectly except for one small issue (that I forgot to mention). Is it possible to incorporate into this code that for the tabs CRO and ICO to disregard the identifier in column F only if in column L the security type says Reserves

<tbody>
</tbody>

 

[Fluff]

Re: Remove Duplicate Macro Help

How about

Sub CopyPasteDuplicates()

   Dim Shts As String
   Dim Ary As Variant
   Dim Cnt As Long
   Dim Cl As Range
   Dim Dic As Object
   
   Shts = "CRO|CROMV|ICO|ICOMV|SICEHY|SICEHYMV|SICCRO|SICCROMV"
   Ary = Split(Shts, "|")
   
   Set Dic = CreateObject("scripting.dictionary")
      For Cnt = LBound(Ary) To UBound(Ary) Step 2
         With Sheets(Ary(Cnt))
            If Cnt < 3 Then
               For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
                  If LCase(Cl.Offset(, 6).Value) <> "reserves" Then
                     If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Nothing
                  End If
               Next Cl
            Else
               For Each Cl In .Range("F2", .Range("F" & Rows.Count).End(xlUp))
                  If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Nothing
               Next Cl
            End If
            Sheets(Ary(Cnt + 1)).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Dic.Count).Value = _
               Application.Transpose(Dic.keys)
            Dic.removeall
         End With
      Next Cnt

End Sub

 

[DRExcel515]

Re: Remove Duplicate Macro Help

Wow thank you so much! That saves me a ton of time!

Cheers!

 

[Fluff]

Re: Remove Duplicate Macro Help

Glad to help & thanks for the feedback