VBA - Cycle Through All Worksheets, Get Unique Values

jaime1182

New Member
Joined
Dec 11, 2007
Messages
24
Office Version
2013
Platform
Windows
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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,465
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,465
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,851
Messages
5,507,703
Members
408,645
Latest member
AndreG06

This Week's Hot Topics

Top