How can i make a macro run all sheets ?

[andrei]

I work with a lot of macros . Sometimes i have excel books with lots o
sheets ( and i mean lots - sometimes more than 20 ) . How can i make m
macros run all sheets in same time

 

[Khuli]

You can refer to all worksheets like this:

Sub test()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Debug.Print sht.Name
Next
End Sub

 

[andrei]

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


Code
-------------------

Sub testme(
Dim myCell As Rang
Dim myRng As Rang
Dim DestCell As Rang
Dim myWord As Strin

myWord = "house

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

For Each myCell In myRng.Cell
With myCel
If LCase(.Value) = LCase(myWord) The
DestCell.Value = .Value & .Offset(0, 1).Valu
Set DestCell = DestCell.Offset(1, 0
End I
End Wit
Next myCel

End Su

 

[Gord Dibben]

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