VBA Help

[Guest]

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

 

[Guest]

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.

 

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

 

[Guest]

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

Thank u!!!

 

[Guest]

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.

 

[Guest]

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

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

 

[Guest]

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.