Extract lines with VBA?

M

Micos3

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
 
B

Bob Phillips

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)
 
M

Micos3

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?
 
B

Bob Phillips

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 Tools>Macro>Macros...

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Micos3

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
 
M

Micos3

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

Bob Phillips

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)
 
M

Micos3

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
 
B

Bob Phillips

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)
 
Top