VBA Help

Hi,
I got this formula that gives me the possibility to extract data when i type
a word, "K". This formula works but i need to put the extracted data to start
in second line, and i cant, i changed where is "H" to many numbers and didn't
make the effect that i want. Someone can help me?
Other thing, with this formula i can control with "C" in wich row it starts
the extracted data of that line to Sheet2, but if i want just some row and
not the whole line, for example i want the rows C, D, E, U, only and not the
whole line, what do i need to change in the whole formula.

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

Thanks

I forgot to ask, can i make a macro in sheet1 to clean sheet2 if i need? This
macro with a security asking if really want to erase f.e.

Thanks Again

Maybe...

Sub comlin3()
dim i as long
dim nextrow as long

with activesheet
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(i, "B").Value = "K" Then
with worksheets("sheet2")
nextrow = .cells(.rows.count,"A").end(xlup).row + 1
end with
.Cells(i, "C").Resize(, 10).Copy _
Worksheets("Sheet2").Range("H" & nextrow)
End If
Next i
end with
End Sub

I used column A in Sheet2 to find the next available row. Change that to a
column that always has data.

Micos3 wrote:
>
> Hi,
> I got this formula that gives me the possibility to extract data when i type
> a word, "K". This formula works but i need to put the extracted data to start
> in second line, and i cant, i changed where is "H" to many numbers and didn't
> make the effect that i want. Someone can help me?
> Other thing, with this formula i can control with "C" in wich row it starts
> the extracted data of that line to Sheet2, but if i want just some row and
> not the whole line, for example i want the rows C, D, E, U, only and not the
> whole line, what do i need to change in the whole formula.
>
> Sub comlin3()
> For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
> If Cells(i, "B").Value = "K" Then
> iRow = iRow + 1
> Cells(i, "C").Resize(, 10).Copy Worksheets("Sheet2").Range("H" &
> iRow)
> End If
> Next i
> End Sub
>
> Thanks

--

Dave Peterson

dim resp as long
resp = msgbox(Prompt:="Do you want to clean sheet2?",buttons:=vbyesno)
if resp = vbyes then
worksheets("sheet2").cells.clearcontents 'or whatever you mean
end if

might be one way???

Micos3 wrote:
>
> I forgot to ask, can i make a macro in sheet1 to clean sheet2 if i need? This
> macro with a security asking if really want to erase f.e.
>
> Thanks Again

--

Dave Peterson

IThanks 'll gonna try now till work, i'll tell anything later.

Thank u!!!

The clean up sheet is Wonderfull, it works just fine!
The other macro, it really goes to 2nd line, if i write in 2nd line it goes
to 3rd. It's fine, but the problem is that only goes the last line of "K".
If i have many "K", only the last one goes to the Sheet2.
And the most difficult, pic rows, is it possible?

Thanks

I don't think I understand your question...

But if you can pick out the column that always has data, you can change this
area:

with worksheets("sheet2")
nextrow = .cells(.rows.count,"A").end(xlup).row + 1
end with

Change that "A" to the column that always has data.

Micos3 wrote:
>
> The clean up sheet is Wonderfull, it works just fine!
> The other macro, it really goes to 2nd line, if i write in 2nd line it goes
> to 3rd. It's fine, but the problem is that only goes the last line of "K".
> If i have many "K", only the last one goes to the Sheet2.
> And the most difficult, pic rows, is it possible?
>
> Thanks

--

Dave Peterson

Sorry i didn't tell nothing in weekend but with no net.....

I didn't knew of that trick
i thought that "A" had relation only to the sheet!!!
So with your anwser I tryed with other letters and with "B", it worked just
fine, so other problem solved :D

To explain better the last problem, with example that is simpler:
A B C D E
1 12-05 K Suplier 10€ 0
2 12-05 Y Suplier 0 5€
3 13-05 W Suplier 0 15€
4 14-05 K Suplier 7,5€ 0
With your solution, that works good, in sheet2 it would appear all rows
after "C", for example, but i want to appear only row C and D in sheet2.
This was the other problem that i named as pic rows.
Is it possible?

Thanks a lot

I want to choose the picked rows or limit to which row i want to copy to
other sheet, because i want to write other things in principal sheet that i
didn't wanted to appear in other sheet.

Now i think that i'm ready to make the program complete. If u want i can
send u a copy, to see the purpous for.

Many Thanks

Thanks for the offer, but no thanks.

But glad you got the problem solved.

Micos3 wrote:
>
> I want to choose the picked rows or limit to which row i want to copy to
> other sheet, because i want to write other things in principal sheet that i
> didn't wanted to appear in other sheet.
>
> Now i think that i'm ready to make the program complete. If u want i can
> send u a copy, to see the purpous for.
>
> Many Thanks

--

Dave Peterson