Copty and Paste to new sheet based on Cell contents

[Jubze]

I have a project for work that we are trying to make as automated and idiot proof as possible.

What we are looking at doing is when a "Patient Watch" is cancelled, the whole row is copied into the "Cancelled" tab in the next available row.

I will be the first to admit that my VB skills are on par with a drunk monkey, so I am begging for some help. I have attached a copy of the workbook I am playing around with.

I was hoping to use formulas and functions instead of VBA, but so far nothing I have tried is working (including any VBA coding that I have found posted around the interweb)


PLEASE HELP

[arlu1201]

Couple of questions -

Do you want the row to be moved or copied?

Do you want the move/copy to happen when the value changes in column AK or will it be done on a click of a button(so that multiple moves/copies can be done at the same time)?

[Pete_UK]

Phew! Your colours are a bit bright !!

The attached file does what you want using 3 basic formulae. I've inserted a new column A in both the start_up and Cancelations sheets, and put this formula in A5 of the start_up sheet:

Please Login or Register  to view this content.

This should be copied down beyond your data to cater for new data being added (the hyphens indicate how far it has been copied), and you could apply protection to the sheet to prevent it being accidentally overwritten, or you could hide the column. The formula just sets up a sequential count of the cancelled records.

The following formula is in A5 of the Cancelations sheet:

Please Login or Register  to view this content.

and again this is copied down as far as you think you might need (with hyphens again). This one finds the rows in the start_up sheet where the cancelled records appear.

The following formula is in B5 of the Cancelations sheet:

Please Login or Register  to view this content.

and this returns the appropriate data from column B where there is a cancelled record. This formula is then copied across into C5:AM5 to bring the data from the other columns. I've copied across the formats from the start_up sheet, but removed the conditional formatting. Then the whole of row 5 has been copied down to row 13, but could be copied further if required.

Hope this helps.

Pete