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,864
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,864
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,543
Messages
5,511,947
Members
408,869
Latest member
MM005

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top