Search : in
By :

Search data in one column, copy data in new

Last answer on Jun 19, 2009 6:03:48 am BST leem, on Jun 18, 2009 1:59:00 am BST 
 Report this message to moderators

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

Configuration: Windows Vista Internet Explorer 7.0

Best answers for « Search data in one column, copy data in new » in :
Adding data from one column using another Show Adding data from one column using another Issue Solution Note Issue I am having a real problem with an Excel formula. I have a worksheet with variable rows and a number of columns. I need to add together the values of particular...
Find The Value of one column in another colum Show
Bsnl data one lan software Show
Leopard OS Installation - Hardisk not recognized ShowLeopard OS Installation - Hardisk not recognized Issue Solution Issue Recently my son had the 160GB HD on his Mac Book Pro 15 replaced with a 320GB. The HD replacement was done by a 3rd party and they used data transfer to copy the...
Transferring data from one hard drive to another ShowTransferring data from one hard drive to another First of all you should use some tools.. transfering data from one hard drive to another is quite simple. Once you have connected the new hard drive to your pc, check your...
Copy an entire partition on Vista ShowCopy an entire partition on Vista Issue Solution Issue It is possible to copy the entire partition on your hard drive with the DVD of vista. It is very useful if you want to change your hard drive in order to restore all data....
Download Driver Lexmark X2350 Show
Download DVDFab Platinum ShowDVDFab Platinum allows you ot copy DVD. The program offers 8 different copy modes: Full disk, main movie, to split, to customize, Clone, to merge and to write data. It can: - copy onto a double layer DVD - copy onto 2 different disks - copy...
Download Blueshell Show
Ask your question Reply

1

mubashir aziz, on Jun 18, 2009 4:59:35 am BST

Can you tell me what you have already tried ?????? Have you used vlookup , Index/match with help of offset ??????


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

2

leem, on Jun 18, 2009 5:29:12 pm BST
  • +1

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

   
Reply to leem

3

 mubashir aziz, on Jun 19, 2009 6:03:48 am BST
  • +1

Sorry don't have enough macro knowledge ......

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz
Ask your question Reply
They need your help