Search data in one column, copy data in new

Closed
leem - Jun 17, 2009 at 08:59 PM
 Bamidele - Jan 2, 2010 at 07:30 AM
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
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
159
Jun 19, 2009 at 01:03 AM
Sorry don't have enough macro knowledge ......

0
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
159
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 ??????


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