Using the Find tool to find, copy and paste...

[PlasticGuy]

Hi there,

We are doing inventory at work and I've exported our products list out to excel. I want to find ALL the "rod" for example. That word is in the description, sometimes the first word and sometimes somewhere else in the description. All the different types of Rod are always in the same place in our warehouse though and we'd like to print off a list of all the rod. How can I use find and search for "rod" and copy the results into a different page or worksheet?

Cheers,
Jeff

 

[NoSparks]

Could try something along the lines of this.
Add a new sheet named Rod.
Adjust your main sheet name and description column as necessary.

Sub RodList()

    Dim rng As Range
    Dim cel As Range
    Dim lr As Long
    Dim nextrow As Long

lr = Sheets("Your Main Sheet").Cells(Rows.Count, "K").End(xlUp).Row
With Sheets("Your Main Sheet")
    Set rng = .Range("K2:K" & lr)
    For Each cel In rng
        If InStr(1, cel.Value, "rod") > 0 Then
            nextrow = Sheets("Rod").Cells(Rows.Count, "A").End(xlUp).Row + 1
            cel.EntireRow.Copy Sheets("Rod").Cells(nextrow, 1)
        End If
    Next cel
End With
End Sub

 

[PlasticGuy]

Thank you for the reply. Any simple way just using the lookup tool? I'm not too savvy with code.

 

[KeepTrying]

Hi,

My solution:

Data sheet sample screen:

Excel 2010
	A	B	C
1	Item nr.	Item name	Description
2	1	Item 1	Rod...........
3	2	Item 2	sdfghsfg
4	3	Item 3	hwshssh
5	4	Item 4	hjdjd
6	5	Item 5	gsghsgh and gsgsg rodhrhshsh
7	6	Item 6	ashrah
Data

Cell Formulas
Range	Formula
A1	Item nr.
A2	1
A3	2
A4	3
A5	4
A6	5
A7	6
A8	7
A9	8
B1	Item name
B2	Item 1
B3	Item 2
B4	Item 3
B5	Item 4
B6	Item 5
B7	Item 6
B8	Item 7
B9	Item 8
C1	Description
C2	Rod...........
C3	sdfghsfg
C4	hwshssh
C5	hjdjd
C6	gsghsgh and gsgsg rodhrhshsh
C7	ashrah
C8	aharshj
C9	agadgadggrod

 

[PlasticGuy]

Hi there,
So other than changing the sheet name in your code to match mine, what else needs to be changed or done?
Thank you

Could try something along the lines of this.
Add a new sheet named Rod.
Adjust your main sheet name and description column as necessary.

Sub RodList()

    Dim rng As Range
    Dim cel As Range
    Dim lr As Long
    Dim nextrow As Long

lr = Sheets("Your Main Sheet").Cells(Rows.Count, "K").End(xlUp).Row
With Sheets("Your Main Sheet")
    Set rng = .Range("K2:K" & lr)
    For Each cel In rng
        If InStr(1, cel.Value, "rod") > 0 Then
            nextrow = Sheets("Rod").Cells(Rows.Count, "A").End(xlUp).Row + 1
            cel.EntireRow.Copy Sheets("Rod").Cells(nextrow, 1)
        End If
    Next cel
End With
End Sub

 

[NoSparks]

You need to specify the Sheet name that has your data and the column with the descriptions.
Put this code in your workbook by right clicking on your data sheet tab and select View Code. Paste this code into the window that comes up.
I've added enough comments to the code that you should be able to follow where and what needs to be done and what each line will do when the code runs.

Sub RodList()

    Dim rng As Range    'the range on sheet with descriptions
    Dim cel As Range    'the individual cells of that range
    Dim lr As Long      'the lastrow of the description range
    Dim nextrow As Long 'the row to copy stuff to on "Rod" sheet

Application.ScreenUpdating = False  'this speeds things up

'make sure we're working on Sheet1
With Sheets("Sheet1")       ' <= sheet name
    'get last row used in the description column
    lr = .Cells(Rows.Count, "K").End(xlUp).Row  '<= column
    'sets the range to work on => this is from row 2 to lr
    Set rng = .Range("K2:K" & lr)       '<= column
    'go down the column a cell at a time
    For Each cel In rng
        'look for "rod" any where in the cell
        If InStr(1, cel.Value, "rod") > 0 Then  'if "rod" is found
            'find the next row to copy to on the sheet named "Rod"
            nextrow = Sheets("Rod").Cells(Rows.Count, "K").End(xlUp).Row + 1  '<= column
            'copy the entire row from Sheet1 to the nextrow on Rod
            cel.EntireRow.Copy Sheets("Rod").Cells(nextrow, 1)
        End If
    'go to the next cell and do everything again
    Next cel
End With

Application.ScreenUpdating = True   'turn screen updating back on

End Sub

Close the VBA window.
Add a new sheet to your workbook, name it Rod
Save the workbook as a .xlsm (macro enabled) file

Press Alt+F8 to bring up the Macros window, select RodList and click Run.

Hope that works.

 

[PlasticGuy]

Thanks very much. I will try it and let you know.
Cheers,
Jeff

 

[KeepTrying]

Hi,

Please ignore my feedback above. You mentioned you're not so familiar with coding. If you use excel 2007 or higher you can use filter. Just add a filter to the worksheet and for the description column: click on filter drop down and type "rod" in search field (without quotation marks).

Then you can manually copy-paste all data on filtered sheet and paste it to another sheet.