Dynamic Summary Sheet

[iliauk]

Hi All,

I have many sheets (for example rows pertaining to a different person and the sheets are split by geography) in a workbook with this kind of format

Col1, Col2, Col3, Col4, Col5

Col5 is of interest - it is a binary yes/no

What I would like to do is create a dynamic summary-sheet which lists all the rows (consolidates) from all the worksheets which have a 'yes' in Col5. All rows (across the book) are unique; so it will never be the case that row 5 on sheet1 is a duplicate of row 3 on sheet 3.

Many thanks!

 

[L. Howard]

Hi iliauk,

Try something like this in a standard module.

Is binary yes/no different than yes, as I spell it here? If so, then Hmmm?

Requires a sheet named dynamic summary-sheet or change the destination sheet name in the code to your sheet name.

Howard

Option Explicit

Sub AllMySheetsColumnE()
'/ me code
Dim ws As Worksheet
Dim FindString As String
Dim Rng As Range
Dim bFoundID As Boolean

FindString = "Yes"
'FindString = InputBox("Enter a search item.")

For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Sheet1" Then
    
    If Trim(FindString) <> "" Then
    
        With ws.Range("E1", ws.Range("E" & Rows.Count).End(xlUp))
            Set Rng = .Find(What:=FindString, _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       SearchOrder:=xlByColumns, _
                       SearchDirection:=xlNext, MatchCase:=False, _
                       SearchFormat:=False)
                       
            If Not Rng Is Nothing Then
            Sheets("dynamic summary-sheet").Range("B" & Rows.Count).End(xlUp)(2) = _
                   Rng.Address & " " & ws.Name & " - " & FindString
            End If
            
        End With
        
    End If
    
  End If
Next ws

End Sub