Assistance with copying and pasting rows to another sheet

[Loukas Char]

Hi!

I have an excel file and the first sheet "sheet1" contains almost 35000 rows and and 12 columns. Six of the columns have text and 6 have numbers or each row.
I want to create a macro which will search for 2 strings (e.g. "serv" or "financ") in the 6 columns with text for each row. If one of the strings is found then I want the code to copy the row with the 12 columns in "sheet2".

I tried to record the macro but it seems that I cannot find a solution.
This is what I have so far:

Sub search()
'
' search Macro

Cells.Find(What:="serv", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows("4:4").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select


Could someone help me please?
I would much appreciate it.

thanks,
Loukas

 

[JLGWhiz]

Hi!

I have an excel file and the first sheet "sheet1" contains almost 35000 rows and and 12 columns. Six of the columns have text and 6 have numbers or each row.
I want to create a macro which will search for 2 strings (e.g. "serv" or "financ") in the 6 columns with text for each row. If one of the strings is found then I want the code to copy the row with the 12 columns in "sheet2".

I tried to record the macro but it seems that I cannot find a solution.
This is what I have so far:

Sub search()
'
' search Macro

Cells.Find(What:="serv", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows("4:4").Select
Selection.Cut
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select


Could someone help me please?
I would much appreciate it.

thanks,
Loukas

Hi Loukas Char, Welcome to the Fourm
Which six columns contain the text?

 

[Loukas Char]

Hi!

from the second column until the 7th

I forgot to mention that I have excel 2010

 

[JLGWhiz]

One other thing, are the strings part of another string or would they be stand alone?

 

[Loukas Char]

These strings are part of other strings.
For example this is a sentence in one column that contains the string "serv", in the word service:

[TABLE="width: 344"]
<colgroup><col width="344"></colgroup><tbody>[TR]
[TD="class: xl64, width: 344"]"Engaged in the manufacture of ***** trucks, buses and construction equipment, drive systems for marine and industrial applications, aerospace components and services"
[/TD]
[/TR]
</tbody>[/TABLE]

My task is to find these strings ("serv" & "financ") in the text in these columns (B:G) and then copy/paste the entire row containing data and text in "Sheet2".

 

[JLGWhiz]

This should work.
Copy this code to your standard code module 1.

Sub findNcopy()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, fLoc As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", sh1.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng = sh1.Range("B2:G" & lr)
    For i = 2 To lr
        For j = 2 To 7
            With sh1
                If InStr(.Cells(i, j), "serv") > 0 Or InStr(.Cells(i, j), "financ") > 1 Then
                    sh1.Range("A" & i).Resize(1, 12).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
                    Exit For
                End If
            End With
        Next
    Next
End Sub

 

[Loukas Char]

It is working fine but it is not working for the string "financ".
And I tried to do it separately by erasing the part for "serv"

(InStr(.Cells(i, j), "serv") > 0 Or)

but it doesn't want to work.

 

[Loukas Char]

Finally, I tried it in the original file but it is not working at all......

 

[JLGWhiz]

It worked OK in test setup where I had at least tw different locactions of each string empbedded in another string. I assume you copied and pasted the code insteacd of retyping, so it should not be a typo problem.

 

[JLGWhiz]

also be sure the sheet names are correct in the code.