Excel VBA Macro to Copy List but not Duplicate

[Youseepooo]

Hello,


I have a worksheet with many sheets that i would like to summarize into one sheet. Essentially i want to go through ranges $D$29:$D$39 and copy the list ( which includes a column of cells with texts & values ) however there are duplicates so i don't want the same cell value/text to appear twice on my summary sheet. I want it to do this for every sheet except 2 which are named "Ref" & "Summary" After going through every sheet's (excluding the 2 i mentioned) i would like it to paste all the values/text on the summary sheet in range
starting in column B31 of "Summary" sheet.

if it could paste it in alphabetical order that would be nice, but most importantly is to capture every different value/text (in all sheets excluding 2 sheets) for the summary sheet.

Thank You very much

 

[Fluff]

How about

Sub Youseepooo()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Ws In Worksheets
         If Ws.Name <> "Ref" And Ws.Name <> "Summary" Then
            For Each Cl In Ws.Range("D29:D39")
               .Item(Cl.Value) = Empty
            Next Cl
         End If
      Next Ws
      Sheets("Summary").Range("B31").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

 

[Youseepooo]

works good so far, it just seems to be picking up a null cell somewhere, is there a way to not make it display empty/null cells.

 

[Fluff]

Yup, like this

Sub Youseepooo()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Ws In Worksheets
         If Ws.Name <> "Ref" And Ws.Name <> "Summary" Then
            For Each Cl In Ws.Range("D29:D39")
              [COLOR=#ff0000] If Cl.Value <> "" Then [/COLOR].Item(Cl.Value) = Empty
            Next Cl
         End If
      Next Ws
      Sheets("Summary").Range("B31").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

 

[Youseepooo]

i believe it works i will play with it more, thanks Fluff second time you helped me

 

[Fluff]

You're welcome & thanks for the feedback