Hi Sam,
Good to see you like it.
The code below has been amended to add the sheet name at the end of the pasted row.
And as a bonus the width of the columns will be adjusted according to the source.
Here is the code:
Sub RunMe()
Dim sh As Worksheet
Dim sValue As String
Dim sCell As Range
Dim lRow As Integer
sValue = InputBox("For which value would you like to create a report:", "Search value")
If sValue = vbNullString Then
MsgBox "You forgot to enter a search value. Run the code again to give it another try."
Exit Sub
End If
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Report for " & sValue
For Each sh In Worksheets
If sh.Name <> "Report for " & sValue Then
Sheets(sh.Name).Select
Set sCell = Sheets(sh.Name).UsedRange.Find(sValue)
If Not sCell Is Nothing Then
firstAddress = sCell.Address
Do
lRow = Sheets("Report for " & sValue).Range("A" & Rows.Count).End(xlUp).Row + 1
sCell.EntireRow.Copy
Sheets("Report for " & sValue).Range("A" & lRow).PasteSpecial
Sheets("Report for " & sValue).Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = sh.Name
Set sCell = Sheets(sh.Name).UsedRange.FindNext(sCell)
Loop While Not sCell Is Nothing And sCell.Address <> firstAddress
End If
End If
Next sh
Sheets("Report for " & sValue).Range("A" & lRow).PasteSpecial Paste:=xlPasteColumnWidths
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Just a pitty that the columns in the report are resized small so i have to enlarge each time, but i guess there is nothing to do about that.
BTW , is there a way that on each row placed in the report there should also be added automatically 1 column at the end with the name of the worksheet where it was taken from so i know where it comes from ( as i get now indeed what i wanted! all texaco bills, but dont know good from WHERE it was taken from..)
So if it would mention at the end of each row the name of the worksheet then it would really be perfect ..
But THANKS SO MUCH ANYWAY !!