Cut and paste on new sheet macro

[hunsnowboarder]

Hi there Everyone!

I have a really bothering problem with excel. I work a lot with this software however I am not yet familiarized with the macro functions unfortunately. So my problem is the following.
I receive each day a file which has 4 types of ID’s. These are 10, 20, 30 and 40. There is only one row of 10 and 40. But there are many rows of 20 and 30 ID’s. The whole thing is on one sheet and the amount of 20 and 30 rows always varies. My job is to cut the rows with 20 and paste it in a new sheet. And then cut the 30’s IDs and paste it on another sheet. The sheets should be named 20 and 30. Is it possible to make a macro which can do this work for me? It would help me a lot in my work! I appreciate every help. Thanks in advance!

[boylejob]

See if this will work for you. You may have to make some modifications seeing that I have no idea what column the 10, 20, 30, 40 are in (I assumed Col A). I also did not know if you had a header row (I assumed you did not).

Please Login or Register  to view this content.

[hunsnowboarder]

Dear Jeff...

Thanx a lot for your effort but unfortunatelly this macro doesnt work properly. Maybe it is because the file which I work with it is quite big, around 2000 rows. The problem is that the macro does not list all of the 30's. It list all the 20's (555 pieces) but for the 30's lists only 555 pieces but it should list all of them (1403 pieces). Can you do something about it?

[jjking57]

If you copy and pasted his code directly, it has one error

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

notice the change from l20Row to l30Row in the PasteSpecial line

[boylejob]

jjking57 is absolutely right. I copied and pasted the Else part of the if statement and

Please Login or Register  to view this content.

should have been

Please Login or Register  to view this content.

therefore the code was using the last row from Sheet 20 instead of the last row from Sheet 30. Some of your 30's ended up getting pasted on top of each other.

I apologize for not catching this. Unfortunately, that's what I get when I answer questions after midnight. Hope this correction works!

Sorry! Thanks jjking57 for catching the error!