Hi,

I've seen threads similar to this before, however I don't know much about macros so I'm having trouble translating similar macros into one that fits the column/cell/sheet names as mine.

So in Sheet1 of my file I want to copy out all rows that contain "Milka" in column I. This column contains other names in each cell, but I just want to take out ones that contain Milka (can contain other names also).

And I want to copy these rows into Sheet 5.

Any help from you excel geniuses would be much appreciated! :)

Hi,

Am Mon, 28 Jan 2013 16:38:38 +0000 schrieb K.Fell:

So in Sheet1 of my file I want to copy out all rows that contain "Milka"
in column I. This column contains other names in each cell, but I just
want to take out ones that contain Milka (can contain other names
also).

try:
Sub Test()
Dim LRow1 As Long
Dim LRow5 As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("I1:I" & LRow1)
If rngC = "Milka" Then
LRow5 = Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Sheet5").Cells(LRow5, 1)
End If
Next
End With
End Sub



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch wrote:

Am Mon, 28 Jan 2013 16:38:38 +0000 schrieb K.Fell:

So in Sheet1 of my file I want to copy out all rows that contain "Milka"
in column I. This column contains other names in each cell, but I just
want to take out ones that contain Milka (can contain other names
also).

try:
Sub Test()
Dim LRow1 As Long
Dim LRow5 As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("I1:I" & LRow1)
If rngC = "Milka" Then

I read the OP's message to read that the cell might contain multiple names,
in which case the above line needs to be changed to this:

If InStr(rngC, "Milka") Then

LRow5 = Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Sheet5").Cells(LRow5, 1)
End If
Next
End With
End Sub

--
- In short, I'm an actress you won't make cry.
- Which brings us back to the question, "Why would I want to cast you?"

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is true."
It's been working for ~29 years now...

--
Eve was not the first to pluck and sample the apple.
Adam was first and he learned by this to put the blame on Eve.

Auric__ laid this down on his screen :
GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is true."
It's been working for ~29 years now...

That's safe in VB but VBA will bite you with that assumption!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion