Code to search data base and copy results to a new sheet

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.

Can you post a sample workbook, with separate worksheets depicting 'as it is now' and 'desired results'? See the yellow banner at the top for instructions.

Thank you for your reply,

Copy of sheet attached. The real sheet will have one tab for each month, I have entered made up date for the first 3 months to give an idea and also made comments in red on the page I want the data to go to.

All the youtube I find just paste the data and over right it, none seems to update based on what is already in there, also doesn't show how to paste certain columns in a certain order, especially if there are 2 tables in the same worksheet in different orders..

Just to see if I'm on the right course, does this meet your expectations for the data transfer from the 3 months to a new spreadsheet?

Hi,
Thank you for the quick reply and the test sheet.

That is what the end sheet will look like yes, but that has pulled through all the renewals, I need it to only pull through ones where the Partner is "RS" it also hasn't pulled through the NB cases in the table below the renewal table.

Give this a try...

Please Login or Register  to view this content.