Macro that will Find, copy, and paste

Matchis

New Member
Joined
Jan 10, 2016
Messages
21
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!!!!!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,563
I'm assuming that you already have Sheet1, Sheet2 and Sheet3 and that the text strings are in column A of Sheet1. Try:
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jan 10, 2016
Messages
21
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

New Member
Joined
Jan 10, 2016
Messages
21

ADVERTISEMENT

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

New Member
Joined
Jan 10, 2016
Messages
21
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

New Member
Joined
Jan 10, 2016
Messages
21

ADVERTISEMENT

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

New Member
Joined
Jan 10, 2016
Messages
21
I change Sheets (1) to sh1 = LIST and Sheets (2) to sh2 = NOTES, but still get run time error???
 

Matchis

New Member
Joined
Jan 10, 2016
Messages
21
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:

Code:
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

New Member
Joined
Jan 10, 2016
Messages
21
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!
 

Forum statistics

Threads
1,136,434
Messages
5,675,834
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top