Copy row if a range of column matches a value

NoraRoberts - Jan 14, 2010 at 02:28 PM
 scottrm20 - Jan 30, 2012 at 10:51 AM
Could you please help me with the following macro.

The following macro basically copies the entire row if the column B contains the value "1". But in my case, I would like to copy the entire row if a range of column (for ex: B via Z) contains the value "1". It would even be better, if the user could input a number/string and then if the column range from B via Z contains that specific string/number - it will automatically copy the entire row that contains that value in a new worksheet/xls file.

Hope to hear from you.


Sub SearchForNumber1()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 1
LSearchRow = 1

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("B" & CStr(LSearchRow)).Value = "1" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select

'Paste row into Sheet2 in next row
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching

End If

LSearchRow = LSearchRow + 1


'Position on cell A3
Application.CutCopyMode = False

MsgBox "All matching data has been copied."

Exit Sub

MsgBox "An error occurred."

End Sub

7 responses

tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 15, 2010 at 02:18 AM
the first code I wrote was looking for cells having the exact same value as the entered string.
here's an update that looks for cells containing the string:
Sub customcopy()
Dim strsearch As String, lastline As Integer, tocopy As Integer

strsearch = CStr(InputBox("enter the string to search for"))
lastline = Range("A65536").End(xlUp).Row
j = 1

For I = 1 To lastline
    For Each c In Range("B" & I & ":Z" & i)
        If InStr(c.Text, strsearch) Then
            tocopy = 1
        End If
    Next c
    If tocopy = 1 Then
        Rows(i).Copy Destination:=Sheets(2).Rows(j)
        j = j + 1
    End If
tocopy = 0
Next i

End Sub