Related:
- Search data in one column, copy data in new
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- Display two columns in data validation list but return only one - Guide
- How to be offline in whatsapp when data is on - Guide
- Www.google.com search video - Download - Video playback
3 responses
I'm a beginner in Excel and tried the following Macro codes - I just need to get it to pick up specific data from the above row and copy it to a new sheet. Thanks for your help
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 = "CAF", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = "CAF" 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
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 = "CAF", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = "CAF" 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
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Jun 17, 2009 at 11:59 PM
Jun 17, 2009 at 11:59 PM
can you tell me what you have already tried ?????? Have you used vlookup , Index/match with help of offset ??????
Jun 19, 2009 at 01:03 AM