Copy rows spanning 35 columns from one sheet to another when cell has a specific value

[jsher44]

Hi All
I hope someone can help with this. I have a workbook with two sheets; the first (called Referrals) has case data recorded across 35 columns, in the last of which (AI) one of two values can be recorded- "NFA" or "STRATEGY MEETING". Of all referrals, only a few cases go to the Strategy Meeting but when they do, I would like them to be automatically copied to the second sheet (called Strategy). After being copied to the Strategy sheet, data is then added to these cases from column AJ onwards up to BF. I have tried different ways of copying the rows using Excel formulas, none of which have worked successfully and I now have a macro (TESTCOPY) which successfully copies ALL the relevant cases (those that are going to Strategy Meeting) between sheets but each time the code is fired, it overwrites any additional data that has already been added in Cols AJ onwards. Can someone advise how cases can be added without overwriting this data?
The macro could be fired by a button; on opening the sheet; or by the input of "Strategy Meeting" value; or by some other way that you think is appropriate.
I've attached a file with some anonymised data
Thanks in anticipation

[Mumps1]

Hello jsher44. Try the attached file. I have added a validation list in column AI so you can choose your value. I have also had to modify the "strategy sheet" a bit because for some reason the code wasn't working with the way it was set up. When you choose "STRATEGY MEETING", the row will be copied automatically to the "strategy" sheet. You should be able to make additions to the sheet without any problems. Let me know how it works out.

[jsher44]

Dear Mumps1 First of all, many,many thanks for this code and so quickly too! It does exactly what I wanted it to. But (there's always a but!)there's an unforeseen hiccup. In hidden column BI of the "Strategy" sheet there's a date calculation formula that I forgot about. This extends down the spreadsheet for 2000 rows. There's a reason for this..but too involved to get into here. The remaining issue is that when rows are copied from the Referrals sheet to Strategy sheet, they overwrite the hidden formulae. Is there any way that copied row can be shortened to say 33 columns (A:AH) before pasting into Strategy?
Thank you again for the work you've put in already.
Kind regards

[Mumps1]

Change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Let me know if it works out.

[jsher44]

Spot on - it works brilliantly. Can't believe how long Ive taken struggling with this and to have it sorted out in a few hours! Mumps1 of Toronto, thanks again.
Kind regards

[Mumps1]

It was my pleasure.