Need help with copying multiple rows to another sheet.

[Stylus]

Hi all.

I have a list with information (about 500 rows). The list has got 12 columns.
I need a macro which finds every occurance of "keyword" in a certain column, copies the whole row and finally pastes all this rows into another sheet.
I have been reading a lot of posts trying to find an answer but I can't seem to find anything about this.

Need help.

Thanks in advance.

/Stylus

 

[DonkeyOte]

You need to use a For Each Loop.

Say your data is in 12 columns A onwards (sheet1) - and the keyword will be contained in Column A

Sub PASTEDATA()

DATA = Sheets("sheet1").Range("A1:A500")
CROW = 1
KEYWORD = "BLAH"

For Each LDATA in DATA

If LDATA = KEYWORD Then

Range("A" & CROW & ":" & "L" & CROW).copy
Sheets("sheet2").Range("A" & CROW).PasteSpecial xlPasteValues
Sheets("sheet1").select

End If

CROW = CROW + 1

Next LDATA

 

[Stylus]

Tanks for the fast answer.
Two problems though;
1. When I try to put ie "radio*" in KEYWORD (in order to find everything that starts with "radio" or "Radio") it doesn't work.

2. In target sheet there is a lot of empty rows (not so strange, btw) and I would like to deleta empty rows or just paste onto the next empty row...

Thanks once again for the fast response.

 

[DonkeyOte]

can you send me the file quickly so I can check it out?

And we can put a count on the target sheet so it knows where the next blank row is.

No probs.

Luke

 

[Stylus]

I'd rather not. It's company information and I don't think my boss would appreciate that..
Thanks anyway, you've been very helpful.

 

[Ekim]

Luke's macro does work. I have made a slight amendment to this line:<pre>
Sheets("sheet2").Range("A" & CROW).End(xlUp).Offset(1, 0)</pre>


I assume that after the row is copied to say Sheet2, the row in sheet1 containing
the word "Radio" will be deleted
and all data moved up.<pre>.
Sub PASTEDATA()

DATA = Sheets("sheet1").Range("A1:A500")
CROW = 1
KEYWORD = "Radio"

For Each LDATA In DATA

If LDATA = KEYWORD Then

Range("A" & CROW & ":" & "L" & CROW).Copy _
Sheets("sheet2").Range("A" & CROW).End(xlUp).Offset(1, 0)
Sheets("sheet1").Select

End If

CROW = CROW + 1

Next LDATA

Worksheets("Sheet1").Activate
FindString = "Radio"
Set b = Range("A:A").Find(What:=FindString, lookAt:=xlWhole)
While Not (b Is Nothing)
b.EntireRow.Delete
Set b = Range("A:A").Find(What:=FindString)
Wend
End Sub


HTH

Mike
This message was edited by Ekim on 2002-10-24 14:23

 

[Stylus]

Thanks a lot.
One problem though; there seems to be a problem with this line;

Sheets("sheet2").Range("A" & CROW).End(xlUp).Offset(1, 0)

A compile errror " = is expected" occurs.
Would really appreciate your help...
Thanks in advance