Need help with copying multiple rows to another sheet.

Stylus

New Member
Joined
Oct 23, 2002
Messages
12
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Oct 23, 2002
Messages
12
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Oct 23, 2002
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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

New Member
Joined
Oct 23, 2002
Messages
12
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
 

Forum statistics

Threads
1,147,815
Messages
5,743,366
Members
423,790
Latest member
kevinlee_5

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
Top