Find Row on another Spreedsheet by User input and copy row

Question

The code is finding the row based on input but not copying it back to my workbook.

Code asks User for the Workbook name to check for line match to the user import. I need to find the row and based on Col A and copy the row to my Workbook. I don't get an error but it not copying the row back

Sub Macro1()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim myValue As Variant

    With Application.FileDialog(msoFileDialogFilePicker)  
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1     
        .Show                
        fullpath = .SelectedItems.Item(1)
    End With

    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    Workbooks.Open fullpath

    Set wb = ActiveWorkbook
    wb.Activate

    myValue = InputBox("Enter Value to lookup")

    wb.Activate    

    RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row

    For i = 1 To RowCount
        Range("a" & i).Select
        check_value = ActiveCell
        If check_value = myValue Then        
            ActiveCell.EntireRow.Copy
            RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
            Range("a" & RowCount + 1).Select
            Sheets("Sheet2").Paste
            Workbooks("wip - copy.xlsm").Activate
            Sheets("Sheet2").Paste
        End If
    Next

    Workbooks("wip - copy.xlsm").Activate
    Sheets("Sheet2").Select       
End Sub

See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for ways to improve your code. Also, a filter would be more efficient here than looping row by row. — BigBen, Sep 29 '19 at 19:55
No Need to loop. Simply use [Autofilter](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s/11633207#11633207) as shown here :) — Siddharth Rout, Sep 30 '19 at 02:32

score 0 · Answer 1 · answered Sep 29 '19 at 20:17

You can do something like this (using Match to find rows of interest):

Sub Macro1()

    Dim wb As Workbook, fullPath
    Dim ws As Worksheet, wsDest As Worksheet
    Dim m As Variant, myValue As Variant

    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        fullPath = .SelectedItems.Item(1)
    End With

    If InStr(fullPath, ".xls") = 0 Then Exit Sub

    Set wb = Workbooks.Open(fullPath) '<< get a direct reference
    Set ws = wb.Sheets(1)

    Set wsDest = ThisWorkbook.Sheets("Sheet2")

    myValue = InputBox("Enter Value to lookup")

    m = Application.Match(myValue, ws.Columns("A"), 0)
    Do While Not IsError(m)
        'got a match - copy row
        ws.Rows(m).Copy _
            wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Offset(1, 0)

        m = Application.Match(myValue, _
               ws.Range(ws.Cells(m + 1, 1), ws.Cells(ws.Rows.Count, 1)), 0)
    Loop

End Sub

Find Row on another Spreedsheet by User input and copy row

1 Answers1