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

 

[Loukas Char]

Ok. I had to delete the first sheet and everything works fine!!!
Thank you very much for your help. You are very kind!
Have a nice day (or night)

 

[JLGWhiz]

Change this:

InStr(.Cells(i, j), "financ") > 1

To this:

InStr(.Cells(i, j), "financ") > 0

and see if it helps.

 

[JLGWhiz]

You are welcome,
Regards, JLG

 

[Loukas Char]

Yep it is running so nice and smooth!!
May you have a nice Christmas and NYE


Regards,
Loukas

 

[Hercules1946]

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

I tried this code and it works fine. However I did notice from the OP that you wanted to remove the rows from the source sheet, as well as copy into the target sheet.
If this is the problem the code needs a little "tweek".

HTH

 

[Loukas Char]

No no no. I want the first sheet untouched so I don't want to remove the rows each time. Maybe I didn't express very clearly what I wanted.
It is working perfectly I think!!

 

[Hercules1946]

No no no. I want the first sheet untouched so I don't want to remove the rows each time. Maybe I didn't express very clearly what I wanted.
It is working perfectly I think!!

Great. I was mislead by the fact that your code in post #1 was using Cut and not Copy.

 

[Loukas Char]

Thank you for the interest Hercules1946. But it is fine that way. Besides if I need to cut instead of copy I just replace the word in the code and it works!

 

[Loukas Char]

Hello to everybody!

I would like, once again, to get some help for the code which searches for text strings. This code checks for specific strings in a specific area: from column C up to column G and from the second line up to the last (which is unknown because it is not stable). Then the rows which have at least one of the strings are copied/pasted, one below the other, to Sheet 2 up to column 50. Have a look at the code.

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") > 0 Or InStr(.Cells(i, j), "component") > 0 _
Or InStr(.Cells(i, j), "part") > 0 Or InStr(.Cells(i, j), "maint") > 0 Or InStr(.Cells(i, j), "install") > 0 _
Or InStr(.Cells(i, j), "repair") > 0 Or InStr(.Cells(i, j), "refurb") > 0 Or InStr(.Cells(i, j), "overhaul") > 0 _
Or InStr(.Cells(i, j), "procur") > 0 Or InStr(.Cells(i, j), "purchas") > 0 Or InStr(.Cells(i, j), "support") > 0 _
Or InStr(.Cells(i, j), "provis") > 0 Or InStr(.Cells(i, j), "lease") > 0 Or InStr(.Cells(i, j), "outsourc") > 0 _
Or InStr(.Cells(i, j), "licens") > 0 Or InStr(.Cells(i, j), "solut") > 0 Or InStr(.Cells(i, j), "solv") > 0 _
Or InStr(.Cells(i, j), "consult") > 0 Or InStr(.Cells(i, j), "advic") > 0 Or InStr(.Cells(i, j), "optimiz") > 0 _
Or InStr(.Cells(i, j), "optimis") > 0 Or InStr(.Cells(i, j), "assist") > 0 Or InStr(.Cells(i, j), "analys") > 0 _
Or InStr(.Cells(i, j), "turnkey") > 0 Or InStr(.Cells(i, j), "deliver") > 0 Or InStr(.Cells(i, j), "design") > 0 _
Or InStr(.Cells(i, j), "develop") > 0 Or InStr(.Cells(i, j), "custom") > 0 Or InStr(.Cells(i, j), "personali") > 0 _
Or InStr(.Cells(i, j), "tailored") > 0 Or InStr(.Cells(i, j), "adjust") > 0 Or InStr(.Cells(i, j), "traini") > 0 _
Or InStr(.Cells(i, j), "courses") > 0 Or InStr(.Cells(i, j), "pre-sal") > 0 Or InStr(.Cells(i, j), "after-sal") > 0 _
Or InStr(.Cells(i, j), "pre sal") > 0 Or InStr(.Cells(i, j), "after sal") > 0 Then
sh1.Range("A" & i).Resize(1, 50).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
Exit For
End If
End With
Next
Next
End Sub

I have 2 issues that I cannot resolve.
1. I cannot change the search area in order to search from column C up to column E for each line, from line 2 up to the last (which as said before is unknown). I tried to change the search area and the iterations but it doesn’t.
2. When I use all these strings it crashes. Do you think I should reduce them somehow? Is there anything else I could do? Any suggestions?

If there is someone who could help I would much appreciate it.

Cheers,
Loukas

 

[Loukas Char]

So could someone give me some help on these issues
I have my excel crashing all the time and I cannot get any results even if I try to change the search area