VBA - Cycle Through All Worksheets, Get Unique Values

jaime1182

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
Solution
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top