Extract lines with VBA?
 

Hi,
I have a db that i want to to extract some lines to other sheet when a word
is typed. I give example in below. When "K" is typed i Want that line 1,4,5
to be extracted to other sheet by order. How do i do this?
A B C
1 Day K
2 Day W
3 Day Y
4 Day K
5 Day K
6 Day Y

Extract lines with VBA?
 

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"C").Value = "K" Then
iRow = iRow + 1
Rows(i).Copy Worksheets("Sheet2").Rows(iRow)
End If
Next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Micos3" wrote in message
...
Hi,
I have a db that i want to to extract some lines to other sheet when a
word
is typed. I give example in below. When "K" is typed i Want that line
1,4,5
to be extracted to other sheet by order. How do i do this?
A B C
1 Day K
2 Day W
3 Day Y
4 Day K
5 Day K
6 Day Y

Extract lines with VBA?
 

I't doesn't worked for me, i'm a newbie in VBA, so i'll tell what i did, i
putted the formula in workbook, and in active sheet, but it didn't happened
nothing in sheet2, like formula says. What did i do wrong?

Extract lines with VBA?
 

I would wrap it as a single macro

Sub myData()
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"C").Value = "K" Then
iRow = iRow + 1
Rows(i).Copy Worksheets("Sheet2").Rows(iRow)
End If
Next i
End Sub

put it in a standard code module, and then call the macro from the worksheet
via ToolsMacroMacros...

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Micos3" wrote in message
...
I't doesn't worked for me, i'm a newbie in VBA, so i'll tell what i did, i
putted the formula in workbook, and in active sheet, but it didn't
happened
nothing in sheet2, like formula says. What did i do wrong?

Extract lines with VBA?
 

Many thanks cos with instructions so detailed even a newbie like me could
made that. The more incredible is that is the first time VBA worked for me, i
guess this says all :D

I only ask 2 things:
Is it possible to choose not the whole line but some rows?
I have to do the same for 2 more sheets, like K, but with other letter, i
will apply this macro, offcourse changing the variables, can i connect the
same button to 3 diferent macros?

Once again, Thanks

Extract lines with VBA?
 

I forget to ask 1 more, can i put the data not to begin in i1st line but in
2nd?
Thanks

Extract lines with VBA?
 

You could setup the test value in a cell and check that. With starting at
row 2

Sub myData()
For i = 2 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"C").Value = Range("A1").Value Then
iRow = iRow + 1
Rows(i).Copy Worksheets("Sheet2").Rows(iRow)
End If
Next i
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Micos3" wrote in message
...
I forget to ask 1 more, can i put the data not to begin in i1st line but
in
2nd?
Thanks

Extract lines with VBA?
 

Isn't working, i don't know why, but i'll gonna try with a new db and macro
now with 3 sheet, after lunch.
Other thing, Is it possible to choose not the whole line but some just some
columns?
Thanks again

Extract lines with VBA?
 

Should be

Sub myData()
For i = 2 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"C").Value = Range("A1").Value Then
iRow = iRow + 1
Cells(i,"C").Resize(,10).Copy Worksheets("Sheet2").Range("A" &
iRow)
End If
Next i
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Micos3" wrote in message
...
Isn't working, i don't know why, but i'll gonna try with a new db and
macro
now with 3 sheet, after lunch.
Other thing, Is it possible to choose not the whole line but some just
some
columns?
Thanks again

Extract lines with VBA?
 

The first formula I did it with 3 sheets :)
so the basics it's working

The last formula u gave i can control where i put to start in sheet2, this
case row B.
But i'm not getting to start in line 2 and i can't choose the rows that i
will extract. I've changed many variables but i can't either of these cases.

Sub comlin3()
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "C").Value = "K" Then - It only works like this, the
Range("A1").Value don't work, and i tryed hard
iRow = iRow + 1
Cells(i, "C").Resize(, 10).Copy Worksheets("Sheet2").Range("B" &
iRow)
End If
Next i
End Sub

Can u help again?

Thanks again