Macro that will Find, copy, and paste

[Matchis]

Hello,

I have a workbook that has two sheets. On the first sheet is a list of approximately 30 text strings (i.e.: customer is not happy, not negotiable, no one cares, etc). The second sheet has multiple columns from A:EF and approximately 785 rows. The data I am searching for is in column J (Notes) on the second sheet.

I am looking for a macro that will do the following:

1. Copy the headers from the second sheet to a brand new worksheet, then,
2. Search through the second sheet in column J (Notes) for all of the multiple text strings that are listed on the first sheet, then,
3. When a match is found, copy and paste that Entire Row to the brand new worksheet created in step one.

I really appreciate any help provided!!

Thank You all very much!!!!!

 

[mumps]

I'm assuming that you already have Sheet1, Sheet2 and Sheet3 and that the text strings are in column A of Sheet1. Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    Sheets("Sheet2").Rows(1).EntireRow.Copy Sheets("Sheet3").Cells(1, 1)
    For Each rng In Sheets("Sheet1").Range("A2:A" & LastRow)
        Set foundRng = Sheets("Sheet2").Range("J:J").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            foundRng.EntireRow.Copy Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

this assumes the list on sheet 1 is in column A. If not, you will need to modify the For Each statement. This adds a new sheet for the copied data.

Sub copyNpaste()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Sheets.Add After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set sh3 = ActiveSheet
sh2.Rows(1).Copy sh3.[A1]
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("J2", sh2.Cells(Rows.Count, 10).End(xlUp)).Find(c.Value, , xlValues, xlPart)
            If Not fn Is Nothing Then
                fn.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
    Next
End Sub

 

[Matchis]

One quick question for JLGWhiz,

You added a comment of "edit sheet name", which part do I edit? The "sh1" or the "sheets." Section? The sheet with the list on it is actually called "list" and the 2nd sheet with the data on it is called "notes". Where'd would I substitute?

 

[Matchis]

I was also wondering if I could copy this macro to a file that I could use over and over each time I get a new report that I need to search?

I have my macros turned on on my excel ribbon that I use for other macros.

 

[Matchis]

Hello,

i entered your code as a module and assigned a button to it but when I click on the button I get run time error 424' object required....how do I fix this?

 

[Matchis]

I have tried each of these and get the same run time error "object required". Can you please explain in a little more detail? This is very new to me!

 

[Matchis]

I change Sheets (1) to sh1 = LIST and Sheets (2) to sh2 = NOTES, but still get run time error???

 

[Matchis]

Hello,

This is what my code looks like. I have it assigned to sheet 1, and I created a Button to Search and assigned this macro to it, but it continuously throws run-time errors:

Sub copyNpaste()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range, fn As Range
Set sh1 = List
Set sh2 = Notes
Sheets.Add after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set sh3 = ActiveSheet
sh2.Rows(1).Copy sh3.[A1]
    For Each c In sh1.Range("*", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("j:j", sh2.Cells(Rows.Count, 10).End(xlUp)).Find(c.Value, , xlValues, xlPart)
        If Not fn Is Nothing Then
            fn.EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

Any help to correct it would be greatly appreciated!!

 

[Matchis]

Hello Again,

I think I forgot this part, but I also need to copy the headers from the 2nd worksheet (Notes) to the 3rd worksheet (Results) so we can determine what the data is!

Again - thank you so much for all of your help!