Cut & paste values with one criteria

[ABSTRAKTUS]

Hi all,

Assume you have column B filled with products and column C with status. Status can be only "IN PROGRESS" or "COMPLETE". At the start it will be all in progress, but when user changes cell next to product to "complete", I'd like this product cut & paste into column E (alphabetically preferred). Is this something from Sci-fi or is it doable?

Thanks.

[alansidman]

In E3

=IF(C3="COMPLETE",B3,"")

Copy down

[pdjacques]

You could go down the vba rout and paste this code into the worksheets change event

Please Login or Register  to view this content.

or if you want don't want to lose your formatting in column E use this code:

Please Login or Register  to view this content.

Hope this helps,
Paul

[ABSTRAKTUS]

This one is too easy mate... I should have mention that column E can't contain formulas as this column will be used for typing in additional products constantly as well as those transfered from col B. Formula is not an option in this case, but thank you for your effort anyway. Any more thoughts?

[ABSTRAKTUS]

Hi Paul, the second code looks promising to me, but there is a little BUT... Where do I stick this code: This workbook module or to a separate module?

Thanks for a quick reply...

[ABSTRAKTUS]

And as far as I understand this is not a "complete code", I have to adapt it to make it work for me. What do I need to change within your code? Sorry for being so inquisitive but I'm a new with VBA.

[watersev]

hi, ABSTRAKTUS, please check attachment, try to change Status column value

[pdjacques]

No problem at all.
First, right click on the worksheet and select view code.
You will see two drop down boxes in the vba window. Drop down the first and select "worksheet".
Drop down the second and select "change".

Paste the code between Private sub line & End sub.
It will check for the word "COMPLETE" every time you make a change to that sheet and carry out the cut paste operation if it is found.

Regards,
Paul

[pdjacques]

If for columns are in different positions you may have to change the numbers within the offset brackets.
It works like this
e.g. Offset(1,2) will find a value one row down and two columns to the right
similarly, offset(-1,-2) will find a value one row up and two columns to the left

I hope this makes sense.

[ABSTRAKTUS]

Got it! Thanks a ton guys for your help! Awesome stuff!

[ABSTRAKTUS]

Oops... it worked on a dummy but didn't on the actual workbook. Actually I need 3 values to cut & paste from one location to another. I've attached dummy with the exactly the same layout as the original workbook. So, if status is "complete", I need values from columns C,D & E transfered to cols H, I & J.

[pdjacques]

I had to modify the code a bit but this should do the trick for you:

Please Login or Register  to view this content.

[ABSTRAKTUS]

Two problems with your code:

1. It overwrites values in target locations (cols H, I & J). Some products might be there already and I don't want to miss them.

2. It doesn't sort H, I & J cols alphabetically.

[watersev]

please check attachment

[ABSTRAKTUS]

On the original workbook debuging mode pops up on the following line:
Range("h11").CurrentRegion.Sort key1:=Range("h11"), header:=xlYes

[watersev]

what is original workbook? The posted file PROBLEM.xls has no such mistake. Does the original workbook has any difference in layout comparing to the posted file?

[ABSTRAKTUS]

Found a problem but don't know how to get round. In cell H61 there is a text "TOTAL". And this screws everything up.

[watersev]

give me a couple of minutes

[ABSTRAKTUS]

see snapshot of my original workbook. Sorry too large to post. Weighs ~10Mb

[watersev]

please check attachment

[ABSTRAKTUS]

Now this line:
Set copy2cell = IIf(Cells(12, "h") = "", Cells(12, "h"), Cells(11, "h").End(xlDown).Offset(1))

[ABSTRAKTUS]

Got it! Sorry this was my fault. Just forgot to type "total" back to H61 wich I had deleted.

The other problem is that code cuts everything from C, D E columns. I mean all formatting, data validation etc. Is it possible to make the code copy, then paste and then clear contents rather than a hard cut.

[ABSTRAKTUS]

I'm at work now and going home in a minute. I'll be back on line in half an hour.

[watersev]

please check attachment

[ABSTRAKTUS]

You're a star mate! Agromnoe sposibo!

[ABSTRAKTUS]

Here you go mate...

[watersev]

please check attachment, the sheet is protected from Product code column to Planning comments column except Status column. Password: 123
Try to change Status.
The code will process correctly one changed cell at a time.

[ABSTRAKTUS]

You didn't get me mate. All worksheet is locked (protected) as contains formulas. Only ranges C12:F60 and H12:Q60 are unlocked (unprotected) as are needed to work with.

[ABSTRAKTUS]

I got it now mate. Just unlocked those ranges and it worked well. Thanks a ton!

[watersev]

ok, here is attachment, unlocked ranges: C12:F60 and H12:Q60, password: 123
Is it anything out there that I miss?

[ABSTRAKTUS]

Yes, just found another bug. The code is actually sorting only col H and the other ranges remain static. Try to put some text in "PLANNING COMMENTS" and when you change status it sorts out col H and your text will stay in the cell in "PLANNING COMMENTS". The code should sort whole rows.

[watersev]

check attachment, sorted range: H12:EH60, unlocked ranges: C12:F60 and H12:Q60, password: 123

Taking into account amount of formulas used I would suggest not to do sorting every time but do it manually after all actions done

[ABSTRAKTUS]

Thanks mate. All good now. Thanks for your effort!

[ABSTRAKTUS]

Another problem...

[watersev]

please check attachment

[ABSTRAKTUS]

There is no attachment or I can't see it on the new interface...

[ABSTRAKTUS]

When status in column F is marked as complete, can you make the code to put text "IN PROGRESS" in column S as well?

[watersev]

please check attachment

[ABSTRAKTUS]

Thanks mate. It works very well! Good job!

[watersev]

if we are done, please mark the thread as Solved:

Rule #9. ... If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.