Search for keyword from prompt in Excel then create new tab with summary

[johnbrownbaby]

I have an excel file with many sheets (tabs). I wanted to create a script in excel, such that when you hit run, a prompt comes up asking for "Text to search for", then after inputting the text, "failed" for example, the script then searches every sheet. Then a summary sheet is created that contains the rows of the cells from the various tabs from the key text search.
Thanks for your help.

 

[johnbrownbaby]

I had tremendous help from Romcel Geluz. The code however repeated the found enteries. Also, how do you go about appending the actual row data to the next column of the found keyword sheet output? Here is is code

Private Sub FindAndCreateReport()' Declare variables we will use to loop through each worksheetDim eWs As WorksheetDim rFound As Range' Declare variables to check if we are done looping through the worksheetDim rLastCell As RangeDim rFirstCell As Range' Declare and prepare the variable to hold the string we are looking forDim strLookFor As StringstrLookFor = InputBox("Text to Search for")If Len(Trim(strLookFor)) = 0 Then Exit Sub' Declare and prepare variables used when creating the reportDim rCellwsReport As RangeDim wsReport As WorksheetSet wsReport = ThisWorkbook.Sheets.AddSet rCellwsReport = wsReport.Cells(1, 1)On Error Resume Next                            '<~ skip all errors encountered' Start looping through this workbookFor Each eWs In ThisWorkbook.WorksheetsIf eWs.Name = wsReport.Name Then GoTo NextSheet '<~ skip if we are checking the report sheet  With eWs.UsedRange    ' Set the lastcell. So we can start the search from the bottom.    Set rLastCell = .Cells(.Cells.Rows.Count)    ' Initial search for the string.    Set rFound = .Find(what:=strLookFor, after:=rLastCell)  End With  If Not rFound Is Nothing Then                 '<~ if we found something then?    ' Set it as the first find.    Set rFirstCell = rFound    ' Write its details to the report through this small sub.    WriteDetails rCellwsReport, rFound  End If  Do    ' Continue looking for more matches    Set rFound = eWs.UsedRange.Find(what:=strLookFor, after:=rFound)    ' If there are matches, write them down the report sheet.    WriteDetails rCellwsReport, rFound  Loop Until rFound.Address = rFirstCell.Address '<~ loop through until the current cell is the first cellNextSheet:NextEnd Sub

Private Sub WriteDetails(ByRef rReceiver As Range, ByRef rDonor As Range)  rReceiver.Value = rDonor.Parent.Name  rReceiver.Offset(, 1).Value = rDonor.Address  Set rReceiver = rReceiver.Offset(1, 0)End Sub