VBA Help

[Micos3]

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

[Micos3]

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]

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

[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

[Micos3]

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

Thank u!!!

[Micos3]

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]

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

[Micos3]

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

[Micos3]

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]

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