Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- http://www.techonthenet.com/excel/macros/search_for_string.php
- Search for a value in a column and copy row to new sheet for all matching values in Excel 2003/XP/2000/97
- Sub SearchForString()
- Dim LSearchRow As Integer
- Dim LCopyToRow As Integer
- On Error GoTo Err_Execute
- 'Start search in row 4
- LSearchRow = 4
- '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("E" & CStr(LSearchRow)).Value = "Mail Box" Then
- 'Select row in Sheet1 to copy
- Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
- Selection.Copy
- 'Paste row into Sheet2 in next row
- Sheets("Sheet2").Select
- Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
- ActiveSheet.Paste
- 'Move counter to next row
- LCopyToRow = LCopyToRow + 1
- 'Go back to Sheet1 to continue searching
- Sheets("Sheet1").Select
- End If
- LSearchRow = LSearchRow + 1
- Wend
- 'Position on cell A3
- Application.CutCopyMode = False
- Range("A3").Select
- MsgBox "All matching data has been copied."
- Exit Sub
- Err_Execute:
- MsgBox "An error occurred."
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement