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


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154390

Microsoft Office Help

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

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

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


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=154390

Microsoft Office Help

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

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