Hi all,
Background: I work for an insurance broker, we all use a single excel sheet to record our renewals and the status of them/ invited, renewed etc. One of our partners also has a master list of all his clients and income, after we have renewed and updated our renewal sheet, we then have to open his fine the clients and do the same, as add any new business cases. This results in a LOT of double keying, and ones being missed.

Our renewal sheet has a tab for quotes, then one each month for renewals and new business that's gone on cover, it then has a summary tab at the end showing each persons states, conversion, retention etc.

What I want to do, is run a code, that say if X person is shown in column D, then copy the data from certain columns, (I don't need them all) to a new spreadsheet.

I can see there are loads of ways on youtube, which are basically a v-lookup in VBA, but I have seen the following errors and think I can see possible solutions.

I need it in chronological order - Add a sort code line at the end

Both sheets need to be open, I will add a sheet in the main renewal sheet, the data then gets pulled to a 3rd party sheet as found a code to allow that.

The data gets overwritten - The partner turns the lines yellow when hes been paid, so overwriting would be an issue - add a cond form to auto turn yellow where the word paid in shown in final column.

Adding a paid box (already in there) at the moment then partner goes through his list, (which is basically just hundreds of rows on a single sheet they arn't split into tabs) which would then be overwritten, therefor a could add a paid option box in our renewal sheet, but he would then have to go through each tab to update taking him longer. - I have added an extra sheet to the renewal books to automate the reconciliation, is there a way, to code it to say, check for X client name on X date, if the premiums and income match, add the word paid in column J and mark the row on the original list (The new sheet I create for rec) ar correct so he can see which ones match and which need loking into. HE does this twice a month and takes around 90 minutes each time at present manually checking.


In basic, I want to create a running 12 month list on a single sheet of all the renewal and NB cases for a set partner copying only certain lines, Ideally, it would just updates as we went, and not overwrite as that would alleviate a lot of issues, but all the research i have done, overwrites it.