You are here:

Excel/VBA to find and copy the row based on data in column

Advertisement


Question
QUESTION: Hi Jan,
I need a VBA code to copy row based on data in column.
The data in column A consists of First name, column B consists of Last , I have data in the  column B of sheet2 which contains first name as well as last name & other details. I need a vba code which will loop through the data in column A, column B and find in the sheet2, if found the whole row should be copied to Sheet3.
For example if I have "Michael" in column A or “Porter”  in column B  of sheet1 and "Michael Porter 45646" in Column B of sheet2. The whole row which contains the data should be copied to sheet3 even if any one word is found.

Best Regards,
Sanjay

ANSWER: What about using INDEX and Match working together:

=INDEX(Sheet2!C$1:C$100,MATCH(A1&"|"&B1,Sheet2!$A$1:$A$100&"|"&Sheet2!$B$1$B$100,0))

Enter this formula using control+shift+enter. Then copy the cell down and to the right.

---------- FOLLOW-UP ----------

QUESTION: Hi Jan,

Thank you for your response.

I need to do it through macro, I am able to record, but the problem is looping through cells and copying the row where the text is found could not be done through recording.

Best Regards,
Sanjay

ANSWER: If you post your current code I will update it to include the looping.

---------- FOLLOW-UP ----------

QUESTION: Hi Jan,

Thank you for your reply.

Please find the code for your reference.

Sub Macro1()
   Sheets("Sheet1").Select
   'The data starts from A2 & it should loop till the non blank cell.
   Range("A2").Select
   Selection.Copy
   Sheets("Sheet2").Select

   'The recorded macro took the cell value instead of cell reference
   Cells.Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False, SearchFormat:=False).Activate

   'Here I need to copy the row where the string is found
   Rows("2:2").Select
   Range("B2").Activate
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Sheet3").Select
   Range("A2").Select
   ActiveSheet.Paste
   Application.CutCopyMode = False
End Sub

Best Regards,

Sanjay

Answer
Hi Sanjay,

Probably like so:

Sub Macro1()
   Dim oCell As Range
   Dim oPrevcell As Range
   With Worksheets("Sheet1")
       'The data starts from A2 & it should loop till the non blank cell.
       'The recorded macro took the cell value instead of cell reference
       Do
         Set oPrevcell = oCell
         Set oCell = .Range("A:A").Find(What:=.Range("A1").Value, After:=.Range("A2"), LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False)
         If oCell Is Nothing Then Exit Do
         If Not oPrevcell Is Nothing Then
         'check whether we've hit the same cell, indicating there is only one match in column A
         'and no match in column B
         If oCell.Address = oPrevcell.Address Then
         'we're not finding things!
         Set oCell = Nothing
         Exit Do
         End If
         End If
       Loop Until oCell.Offset(, 1).Value = .Range("B1").Value
       If Not oCell Is Nothing Then
         'Here I need to copy the row where the string is found
         Intersect(.UsedRange, oCell.EntireRow).Copy Destination:=Worksheets("Sheet3").Range("A2")
         Application.CutCopyMode = False
       Else
         MsgBox "No match found"
       End If
   End With
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.