Solved: FIND, COPY AND PASTE

[SDave]

Morning all,

I was wondering whether or not someone could assist me.

I need to put together a copy and paste macro, only for specific words.

The macro should search column G in sheet1, for say, HELLO, GOODBYE, MORNING, AFTERNOON, and then copy and paste special the contents of the row into sheet2. The rows in sheet1 begin at C12 and run through to AF4000.

Any help would be much appreciated.

Thanks.

[Bob Phillips]

How do you determine whether the word goes in C12, D12, etc?

[SDave]

Hi xld,

What I would like is for the entire row to be copied into sheet2 if any of those words are found in column G.

So if HELLO is found in G12, then C12:AF12 should be copied into sheet2.

I hope that makes sense?!

[Bob Phillips]

Public Sub ProcessData()
Dim ary As Variant
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With ActiveSheet

ary = Array("HELLO", "GOODBYE", "MORNING", "AFTERNOON")
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
NextRow = 1
For i = 1 To LastRow 'LastRow to 1 Step -1

If Not IsError(Application.Match(.Cells(i, "G").Value2, ary, 0)) Then

.Cells(i, "C").Resize(, 30).Copy Worksheets("Sheet2").Cells(NextRow, "A")
NextRow = NextRow + 1
End If
Next i

End With

End Sub

[SDave]

Thanks xld, your a star.

Can I just ask one more quick question.

Would it be possible for the values to be pasted in column C, for which the starting point would be C12?!

I've tried amending the following line:

Cells(NextRow, "A")

however I keep receving a Run Time Error 1004 > App Defined or Object Defined Error.

[Bob Phillips]

Public Sub ProcessData()
Dim ary As Variant
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With ActiveSheet

ary = Array("HELLO", "GOODBYE", "MORNING", "AFTERNOON")
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
NextRow = 12
For i = 1 To LastRow 'LastRow to 1 Step -1

If Not IsError(Application.Match(.Cells(i, "G").Value2, ary, 0)) Then

.Cells(i, "C").Resize(, 30).Copy Worksheets("Sheet2").Cells(NextRow, "C")
NextRow = NextRow + 1
End If
Next i

End With

End Sub

[SDave]

OK, now I feel stupid!!!

Thanks xld - much appreciated.