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.