copy row data to another worksheet if column two data begins with I/

Good evening everyone.

I found an old thread in the forum that almost matches my concern. However, despite trying to tweak the code to suit my specific needs, I cannot make it work the way I like it to..

I hope you guys can assist me on this.

Problem:

If data on Column B begins with "i/", then copy data on columns A, B, C, D & G only in another worksheet in the same order. Please see image for my concern.

excel problem.jpg

I have also attached the sample answer I found in this older thread: http://www.excelforum.com/excel-prog...fic-range.html

Move As.xls

Thank you very much!

Try the attached. I have assumed that the raw data is on sheet1 and output would be on sheet2.

Wow!

Thanks for the quick reply! I tested the code you sent and it works! However, since I will be transferring the code to my actual worksheet, I suddenly worried if changing the worksheet names will have an effect on the code? I have attached the excel file you made, AB33, showing Sheet 1 as "Unit Prod" and Sheet 2 as "Master List" to show you what my actual worksheet looks like. Both have pre-existing column headings on row 5. There are other worksheets in file as well.

Hope you can ease my mind on this?

Thanks again for the prompt response! :Move As.xls

Amended code:

Hi, AB33!

I've been looking for something like this for a week, though I'd like to know, is it possible to replace the ones copied earlier? instead of just creating new rows.
For example : I would need to have it start copying to B4 and down every time.

Best Regards
Kimzi

Kimzi,
Sorry, I would not able to reply on this thread as it is against forum's rule to post on someone else's thread.
Please start your own thread and attach a sample. I will look out for your thread and write you a code.

I'm really sorry, I will do so!

Hi AB33!!

Your code works perfectly!!!

Thank you! Thank you!

The code runs for 15 seconds since it attempts to get the data from columns "A", "B", "C", "D" & "IN", and there are 1400 rows in the worksheet.

Faster code:

wow.

From 15 seconds to a blink of an eye.

The new code you gave me rocks!

However, I encountered 2 errors one of which I was able to fix (I think), while the other eludes me:

1) the new, faster code transferred all the data to the Master List worksheet as expected but it erased all the column headings in the Master List worksheet.
My solution: remove this line of code:
.UsedRange.Offset(1).ClearContents
I hope it is correct.

2) My column A data are all 9-digit numbers that begin with 2-4 zero digits like so: 000123456. the new code still copied the data as expected but the result now just looks like this: 123456. The zeroes at the beginning were all removed.

Hope you can still assist, AB33!

Hi, I dunno if it's ok for me to reply but if you write:

Please Login or Register  to view this content.

It'll start removing data from line 6 and down.

Kimzi has answered your first question. I have amended the code for the second request.

Thank you very much AB33 & Kimzi! It works flawlessly now.