VBA - Cycle Through All Worksheets, Get Unique Values

[jaime1182]

Hi guys

I am trying to cycle through all the worksheets in my workbook and copying all the unique values from Column C into a new worksheet.

Is there a way to get a macro to do that instead, and then paste it in a new worksheet so that:
- the other worksheet name is in the header and,
- relevant unique Column C values from that worksheet is listed below it?

Example:

[table="width: 500, class: grid"]
[tr]
[td]Worksheet1[/td]
[td]Worksheet2[/td]
[/tr]
[tr]
[td]AAA[/td]
[td]AAA[/td]
[/tr]
[tr]
[td]BBB[/td]
[td]CCC[/td]
[/tr]
[tr]
[td]DDD[/td]
[td]FFF[/td]
[/tr]
[tr]
[td][/td]
[td]EEE[/td]
[/tr]
[/table]

This way I can tell what values are present in each worksheet without having to open each one.

Thanks!

 

[Fluff]

How about

Sub jaime1182()
   Dim Ws As Worksheet, Uws As Worksheet
   Dim Cl As Range
   Dim Dic As Object
   Dim i As Long
   
   Sheets.Add(Sheets(1)).Name = "Unique"
   Set Uws = Sheets("Unique")
   With CreateObject("Scripting.dictionary")
      For Each Ws In Worksheets
         If Not Ws.Name = Uws.Name Then
         i = i + 1
            For Each Cl In Ws.Range("C2", Ws.Range("C" & Rows.Count).End(xlUp))
               .item(Cl.Value) = Empty
            Next Cl
            Uws.Cells(1, i).Value = Ws.Name
            Uws.Cells(2, i).Resize(.Count).Value = Application.Transpose(.Keys)
            .Removeall
         End If
      Next Ws
   End With
End Sub

 

[jaime1182]

That works!!! Thanks heaps!

 

[Fluff]

You're welcome & thanks for the feedback