Assistance with copying and pasting rows to another sheet

Loukas Char

New Member
Joined
Dec 15, 2013
Messages
22
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Upvote 0
One other thing, are the strings part of another string or would they be stand alone?
 
Upvote 0
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:

"Engaged in the manufacture of ***** trucks, buses and construction equipment, drive systems for marine and industrial applications, aerospace components and services"

<colgroup><col width="344"></colgroup><tbody>
</tbody>

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".
 
Upvote 0
This should work.
Copy this code to your standard code module 1.
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") > 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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
also be sure the sheet names are correct in the code.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top