Cut, Paste Row based on cell value

[gv29]

Hi,

I am struggling to do a simple thing.

I have a sheet with a list of project called Portfolio and another with the same structure called Prospect & Closed.

I would like to cut and paste the rows from the Portfolio sheet at the end of the Prospect & Closed one if the rows have either the value "Closed" or "Removed" in column B of the Portfolio Sheet.

My data starts at row 7 in both sheets and the number of row is dynamic.

I have searched for solution but none seem to work. The closest I got was with:

Please Login or Register  to view this content.

This code currently gives me a Run-time error 1004.

Any would be greatly appreciated as I am a bit of a VBA beginner.

Thanks

[vcoolio]

Hello GV,

Try the following simple Auto Filter code (activate it from your active sheet "Portfolio"):-

Please Login or Register  to view this content.

As you can see, I've used the actual sheet code to reference the sheets but you can change these to your sheet tab names if you like.

I've attached a mock-up work book for you to peruse based on what I believe your work book to look like. Click on the Copy/Paste button to see it work.

Do you need the "used" data cleared from the Portfolio sheet? If so, add the following line of code:-

Please Login or Register  to view this content.

just above this line of code:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

[gv29]

Hi Vcoolio,

Thanks heaps for your answer. it works like a charm.

I did add the line to remove "Used Data".

One last thing is there a way to paste only the values with the format?

Thanks,

Gv

[vcoolio]

Hello GV,

This slight modification to the code should do that for you:_

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[gv29]

Thanks a lot for that slight modification.

Cheers,

GV

[vcoolio]

Hello GV,

No worries. Glad that I could help.

Cheerio,
vcoolio.

[gv29]

Hi,

I have an additional question.

Before copying and pasting, I would like to check wether there are any project to close or remove and display a warning message and exit the macro if there is nothing to transfer.

I have the following code but it doesn't seem to work and I can't figure why

Please Login or Register  to view this content.

Thanks again for your help,

[vcoolio]

Hello GV29,

Try changing this :-

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

That should get the message box to show up.

Cheerio,
vcoolio.

[gv29]

Thanks for your prompt answer.

Just tried to change it to

Please Login or Register  to view this content.

but it is still not working properly.
It displays the warning message regardless of wether their are project with closed, removed or prospects or not.

[vcoolio]

Hello GV,

I was wondering if that would be the case. Anyway, I've knocked up the following incorporating warnings with the code I supplied earlier:-

Please Login or Register  to view this content.

You'll get two message boxes. One warning you that not all cells are marked for transfer and when you click OK, a second message box will appear asking if you wish to continue anyway (you may have a couple of rows that you may want to transfer anyway). If you click on NO, the sub will be exited.

If the whole data set (as per my test work book at least!) is marked for transfer, you'll get the second message box only.

For whatever reason, the site's file uploader does not work, so following is a link to my updated test work book for you to peruse:-

https://www.dropbox.com/s/ravrva671v...r%29.xlsm?dl=0

Cheerio,
vcoolio.