5 Sheets Find only values that appear on all sheets with VBA
Results 1 to 2 of 2

Thread: 5 Sheets Find only values that appear on all sheets with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 5 Sheets Find only values that appear on all sheets with VBA

    I have 5 Sheets and want to find only the values that appear in column A on all sheets with VBA, then copy those instances and create a list on a new sheet.

    Any Code help would be appreciated.

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,813
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: 5 Sheets Find only values that appear on all sheets with VBA

    Try this:_
    NB:- This code will create a sixth sheet "Master" for the results.
    Code:
    Sub MG16Jul46
    Dim sht As Worksheet, Dic As Object
    Dim Rng As Range, Dn As Range, n As Long, c As Long
    Set Dic = CreateObject("scripting.dictionary")
    Dic.CompareMode = vbTextCompare
    
    For Each sht In Worksheets
    c = c + 1
       If c > 5 Then Exit Sub
        With sht
            Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
         End With
    
    For Each Dn In Rng
        If Not Dic.Exists(Dn.Value) Then
            Dic.Add Dn.Value, c
        Else
           If InStr(Dic(Dn.Value), c) = 0 Then Dic(Dn.Value) = Dic(Dn.Value) & c
        End If
    Next
    Next sht
    Dim K As Variant
    Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Master"
    Set sht = ActiveSheet
    
    For Each K In Dic.keys
        If Dic(K) = 12345 Then
            n = n + 1
            Sheets("Master").Cells(n, "A") = K
        End If
    Next K
    End Sub
    Regards Mick

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
  •