Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myWord As String
Dim wksht As Worksheet

myWord = "house"
For Each wksht In ActiveWorkbook.Sheets
With wksht
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
Set DestCell = .Range("C1")
End With

For Each myCell In myRng.Cells
With myCell
If LCase(.Value) = LCase(myWord) Then
DestCell.Value = .Value & .Offset(0, 1).Value
Set DestCell = DestCell.Offset(1, 0)
End If
End With
Next myCell
Next wksht

End Sub


Gord Dibben MS Excel MVP

On Mon, 16 Nov 2009 15:35:23 +0000, andrei
wrote:


Actually i have this macro . It concatenates rows if given keyword is
found in A column and puts result in C column . That chages should i do
to run all sheets once ? Now i have to change the name of the sheet
every time i need to run macro



Code:
--------------------

Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myWord As String

myWord = "house"

With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
Set DestCell = .Range("C1")
End With

For Each myCell In myRng.Cells
With myCell
If LCase(.Value) = LCase(myWord) Then
DestCell.Value = .Value & .Offset(0, 1).Value
Set DestCell = DestCell.Offset(1, 0)
End If
End With
Next myCell

End Sub

--------------------