Copying a row in Excel using VBA

[sarah2010]

Hi folks,
I got an excel file which has a bunch of data. I would like to find the items in column "A" which has the value "sarah" & copy the entire row & paste it in a new file. Attached is the sample file.
Note: The pattern of the data (in column A) need not be having a same interval. Can somebody help me out with this? Thanks

[GTO]

Greetings Sarah,

I see that you just joined Welcome to vbaexpress!

...The pattern of the data (in column A) need not be having a same interval.

I'm sorry, but I didn't quite understanf that last bit. Will 'sarah' always be at the start of the string?

Mark

[sarah2010]

Hey Mark! thanks for welcoming me to the forum!
I meant to say that the pattern in which the data in column A which has the value "sarah" need not be of uniform interval. like say, it may exist once in every 3 rows or 4 or anything. so, the interval will be random. Also, "sarah" need not be the starting string always. . Thanks for your attention!

[GTO]

If "sarah" may be anyplace (start, mid, end) in the string in the cell, let's try using InStr (In String). See VBA help for a description.

You could also use AutoFilter with "contains", but I chose just to loop thru the rows.

In a Standard Module:

Option Explicit
    
Sub exa()
Dim _
wksDest     As Worksheet, _
wbDest      As Workbook, _
rngSource   As Range, _
lRow        As Long, _
lCol        As Long, _
i           As Long, _
ii          As Long
    
    '// Set a reference to a newly created one-sheet workbook.                      //
    Set wbDest = Workbooks.Add(xlWBATWorksheet)
    '// Set a reference to the sheet within.                                        //
    Set wksDest = wbDest.Worksheets(1)
    
    '// Change source sheetname to suit.                                            //
    With ThisWorkbook.Worksheets("Sheet1")
        '// Presumes we are safe in using the last cell in Col A to set the bottom  //
        '// row in our range, and uses the last Col in row 1 with data to set       //
        '// the rightmost col in our range of interest.                             //
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        '// Set a reference to the range we want to look thru.  Actually, I was     //
        '// writing a bit fast, this could easily be just Col A, as we are only     //
        '// looking for "sarah" there...                                            //
        Set rngSource = .Range(.Range("A2"), .Cells(lRow, lCol))
        
        '// Copy the header row..                                                   //
        .Range("A1").EntireRow.Copy wksDest.Range("A1")
        ii = 1
        
        For i = 1 To rngSource.Rows.Count
            '// Use InStr to see if "sarah" is anyplace in the string in each cell  //
            If InStr(1, rngSource(i, 1).Value, "sarah", vbTextCompare) > 0 Then
                '// Use a counter to keep track of where we need to paste the next  //
                '// row.
                ii = ii + 1
                rngSource(i, 1).EntireRow.Copy wksDest.Range("A" & ii)
            End If
        Next
    End With
End Sub

Hope that helps,

Mark

[sarah2010]

Thanks Mark, it works to my expectation in the sample file. I'll test it in real case scenarios & let you know. Thanks again for your time & help!!!

[sarah2010]

Hey Mark, thanks a lot for the help. It works fine with the real case scenerios with minor tweaks. Sincerely apreciate your time!

[GTO]

Glad to be of help, and again, welcome to the forum