VBA - Cycle Through All Worksheets, Get Unique Values

jaime1182

New Member
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:

Worksheet1Worksheet2
AAAAAA
BBBCCC
DDDFFF
EEE

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

Thanks!
 

Fluff

MrExcel MVP, Moderator
How about
Code:
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
 

Some videos you may like

This Week's Hot Topics

Top