VBA - Cycle Through All Worksheets, Get Unique Values
Results 1 to 4 of 4

Thread: VBA - Cycle Through All Worksheets, Get Unique Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2007
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Cycle Through All Worksheets, Get Unique Values

    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:

    Worksheet1 Worksheet2
    AAA AAA
    BBB CCC
    DDD FFF
    EEE

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

    Thanks!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA - Cycle Through All Worksheets, Get Unique Values

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Dec 2007
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Cycle Through All Worksheets, Get Unique Values

    That works!!! Thanks heaps!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA - Cycle Through All Worksheets, Get Unique Values

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •