Macro to search mulitple sheets

[Jhansen5452]

Having trouble with a macro that will search row k 12 sheets for a variable. Any time the variable matches I need it to copy to a seperate sheet. This is a monthly parts scrapping log and am trying to get running costs per dept. responsible

 

[pboltonchina]

Try this on a copy of your data

Sub FindAllSheets()
    Dim Found As Range, WS As Worksheet, LookFor As Variant
        LookFor = InputBox("Enter value to find")
            
            If LookFor = "" Then Exit Sub
            
            '   Clear or Add a Results sheet
            If SheetExists("Search Results") Then
              Sheets("Search Results").Activate
              Range("A2").Select
              Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
              Selection.ClearContents
              Range("A1").Select
             Else
                Sheets.Add after:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Search Results"
            End If
            
        Application.ScreenUpdating = False
           For Each WS In ActiveWorkbook.Worksheets
                If WS.Name <> "Search Results" Then
                     Set Found = WS.Cells.Find(What:=LookFor)
                     If Found Is Nothing Then
                         Range("D5").Select
                     Else
                         Found.EntireRow.Copy Sheets("Search results").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                         Found.EntireRow.Interior.Color = vbYellow
                     End If
                End If
            Next WS
            
    Range("A1").Select
     Application.ScreenUpdating = True
                 
End Sub