Search data in one column, copy data in new [Closed]

Report
-
 Bamidele -
Hello,

How can I search for a specific word i.e. "CAF" in one colum and have specific associated data copied into a new sheet. My problem is that the specific data I want to have copied to a new sheet is located in the row above where the search word is.

For example: search under column E for "CAF" - let's say one appears on cell E10, now I want to have data in Column B9, C9, and H10 copied into a new sheet

3 replies

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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4026 users have said thank you to us this month

mubashir aziz
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Sorry don't have enough macro knowledge ......

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
can you tell me what you have already tried ?????? Have you used vlookup , Index/match with help of offset ??????


Visit http://www.dataentryforall.net for your <a href=" http://www.dataentryforall.net">Data entry jobs</a>