Here's another change that allows the rows from the first 41 spreadsheets
(only the first 30 columns are copied, but you can change that to make it
bigger or smaller) to be copied to Column B of results, with the name of the
spreadsheet in Column A:

Sub copyrows()
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
Dim sht
Dim myrange As Range
Dim C

Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Results"
MyValue = "WhatImLookingFor"
For x = 1 To 41
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = Range(C, C.Offset(0, 30))
Else
Set CopyRange = Union(CopyRange, Range(C, C.Offset(0, 30)))
End If
End If
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "B").End(xlUp).Row + 1
CopyRange.Copy Destination:=Sheets("Results").Range("B" & LastRow)
Sheets("Results").Cells(LastRow, 1).Value = sht.Name
Set CopyRange = Nothing
End If
Next
End Sub

--
Rich Locus
Logicwurks, LLC


"Rich Locus" wrote:

Hello again :)

Here's a minor tweak to the code from Mike H that does a Paste Special for
values only and doesn't change colors, fonts or anything else:

Option Explicit
Sub copyrows()
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
Dim sht
Dim myrange As Range
Dim C

' Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Results"

MyValue = "WhatImLookingFor"
For x = 1 To 2
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Row + 1
CopyRange.Copy
Sheets("Results").Select
Cells(LastRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells(1, 1).Select
Set CopyRange = Nothing
End If
Next
Sheets(1).Select
Cells(1, 1).Select

End Sub

--
Rich Locus
Logicwurks, LLC


"newlearner01" wrote:

Yes, it will be great if we have that addition option.

BTW, I would like to add the reference sheet names in the column A, and the
copied rows will start from column B of new sheet ("result" in your example)
so that we know from where those copied rows come. How can I add sheet names
to the Set CopyRange statement?

Thanks.

"Rich Locus" wrote:

Mike:

Love the speed of the copy solution for this post. Do you know how to just
copy the values and not the formatting? The copy statement in your example
copies everything, including formatting and cell colors. Is there a way to
copy that works more like a "Paste Special, values only?"

--
Rich Locus
Logicwurks, LLC


"Mike H" wrote:

Hi,

try this

Sub copyrows()
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Results"
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
MyValue = "Somestring"
For x = 1 To 41
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Row + 1
CopyRange.Copy Destination:=Sheets("Results").Range("A" & LastRow)
Set CopyRange = Nothing
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"newlearner01" wrote:

Hi,

I am a new in VBA and I am wondering whether someone can help me?

My workwork has 45 sheets. I would like to create a macro which will search
for string value in the second column ("B") of the first 41 sheets and if it
finds the matches it will copy entire row and paste to new sheet in same
workbook.

Thanks in advance.