Copy and paste row based on cell value

Hi all,

I have a workbook with a worksheet ("work1") with names in Column A and serial numbers in Column B.

In this workbook is a second worksheet ("work2") with another list of serial numbers.

I need the macro to look for serial numbers in work1 (column B) from work2.

If cell is found, cut row out of work1 and paste in new worksheet.

Thanks!

1) On sheet1 make sure row1 is a "title row", not part of the data.

2) In empty column C, put this formula in C2 and copy down:

=ISNUMBER(MATCH($B2, 'Spreadsheet 2'!$A;$A, 0))

A series of True/False answers will appear.

3) Click the top of the new column and select Data > Filter > AutoFilter

4) Filter the new column by TRUE

5) Highlight all the visible data rows from A:B and cut them, paste to another sheet

6) Turn off the AutoFilter and clear the new column.


You can actually record those steps into a macro pretty easily.

From the steps already provided, let's see you at least use them in your own workbook to do the filtering once and cut/paste to the desired destination. Let it record you doing it twice.

Post that macro and I'll add some error checking for you.

See, it's so much easier to code when we can see what you did. Make sure you already have sheets called "Sheet1", "Sheet2", and "Removed".

Code removed...see below

Wow. Impressed to say the least.

How does LR function find the number of rows exactly? i've never seen this code before. i have like 20 columns, many with blank cells (including the one that we're referencing), so I fear it will miss some. One column (column J) will always have the full cells/true number of rows though.

Feeling happy about this

I also notice that you don't use the LR function again after you define it in the beginning. right?

Originally Posted by contra76

How does LR function find the number of rows exactly? i've never seen this code before. i have like 20 columns, many with blank cells (including the one that we're referencing), so I fear it will miss some. One column (column J) will always have the full cells/true number of rows though.

The LR variable is searching from all the cells from the bottom of the worksheet upward until it finds a cell with anything in it, then storing that row in the LR.

I also notice that you don't use the LR function again after you define it in the beginning. right?

The LR was supposed to appear in a couple of lines of code after that, but missed the first posting, don't know how I managed that. Here's the corrected code along with a different copy/paste/delete from old sheet method that shouldn't include any of the hidden rows.

Option Explicit

Sub MyFilter()
Dim LR As Long, BR As Long, Matches As Boolean

With Sheets("Sheet1")
'find range of data on sheet
    LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'add key column
    .Range("AA1") = "Key"
    .Range("AA2:AA" & LR).FormulaR1C1 = _
        "=ISNUMBER(MATCH(Sheet2!R1C26,'[income nums.xlsx]Sheet1'!R1C1:R18C1,0))"
'filter for matches
    .Range("AA:AA").AutoFilter
    .Range("AA:AA").AutoFilter Field:=1, Criteria1:="TRUE"
'cut matches to another sheet
    BR = .Range("AA" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then
        With .Range("AA2:AA" & BR).SpecialCells(xlVisible)
            .EntireRow.Copy _
                Sheets("Removed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .EntireRow.Delete xlShiftUp
        End With
        Matches = True
    End If
    
'turn off filter
    .AutoFilterMode = False
    .Range("AA:AA").ClearContents
End With

If Matches Then MsgBox "Matches moved" Else MsgBox "No matches"
    
End Sub

delete this post plz