VBA Help - Looking up data between sheets, and perform a function if data is/isnt found.

[chickendippaz]

Hi all,

I am looking for some help with writing a macro to copy and paste some data between worksheets if it meets certain criteria. I have been trying to write a macro that works in a loop as the amounts of data I am refering to will vary.

Basically, I have 3 sheets;

IMPORT - this contains a list of unpaid invoices and various related data, it updates daily via SQL query linked to a database.

Chasing Data - this contains the same list as the import tab, but also includes various notes next to each invoice. It is updated manually (at present) by copying the data from the IMPORT tab and pasting it in then adding notes manually.

Paid Invoices - this contains any invoice which has been paid. This contains any invoices on the Chasing Data tab but not on the IMPORT tab.


What I aim for my macro to do is this;

Look up each invoice contained on the Chasing Data tab on the IMPORT tab, if it is not there then move the whole row and paste it on the next blank row on the Paid Invoices tab.
Look up each invoice on the IMPORT tab to see if it is contained on the Chasing Data tab, if it is not then copy the whole row and paste it.
If the invoice is on the IMPORT tab and the Chasing Data tab then update certain columns containing the aged debt statistics for that invoice.

It seemed simple at first but I have struggled thus far as the number of rows of data on the import tab can vary from day to day so I need to make my macro dynamic.

I would be grateful for any ideas on how to begin writing the macro to perform this function?

 

[oldbrewer]

Before I retired I analysed accidents and incidents in a major company (around 100 per day). We started with date location and incident, later adding date investigation started, status of investigation ( within time, overdue, completed and reported) - separate columns flagged overdue number of days and various pivot tables and other analysis aids reprted on CURRENT STATUS TODAY. Actions arising from investigation were also listed and similarly tracked. The one spreadsheet provided all info needed on a real time basis.