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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
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
42,835
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,679
Messages
5,488,226
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top