Adjust a macro to work with dynamic ranges

[JPayne78]

Hi all this is my first post so please bare with me if I ramble too much or am not very clear, I will try to explain in as much detail as possible......
I am pretty new to working with VBA and Macro's and wondered if anybody could help. I have a worksheet (Pending) that gets updated daily from a second worksheet (Payments) and want to use a Macro to make the whole process a lot quicker, as I have several worksheets that I have to do similar actions for so I could use the same Macro on these worksheets to cut the time spent updating them considerably. In a nutshell the "Pending" worksheet contains a list of reference numbers in column B starting from Row 7 and the "Payments" sheet contains all the payments that have come in that day with reference numbers that correspond to Column B in the "Pending" worksheet. I currently run a Vlookup in the "Pending" sheet to match references from the "Payments" sheet and filter out any N/A. I would like to be able to run a macro to do this automatically, but when I have recorded the Macro it will only work if the number of rows in each sheet is the same everyday, but this differs and so one day there could be 10 rows on the payments sheet and 80 rows on pending, then the next day 40 rows on the payment sheet and 240 rows on pending. Is there a better way of doing this that can be automated, or any VBA code I could put into the macro to make it run for all the rows with a reference number

Thanks in advance

[Rem0ram]

It would be much easier to understand if you could post with a sample book.

[JPayne78]

Hi here are examples of the 2 worksheets attached. Basically what I need to do is match the files in the "Payments" worksheet of the "New packs in" workbook with the "pending" sheet of the "pending & accepted" Workbook using the reference number - Column A in the "new packs in" workbook and Column B in the "Pending & Accepted" workbook. Any duplicates then need to be cut from the "Pending" worksheet in "Pending and accepted" and pasted to the bottom of the "Accepted" sheet and the blank rows deleted, then any files on the "payments" worksheet that are not matched on the "Pending" Worksheet need to be added to the bottom of the "Pending" Worksheet. Then the whole process needs to be repeated using the "Packs" Worksheet on the "New packs in" Workbook.
The main problem I am encountering is that the number of rows in all the worksheet vary on a daily basis.

Hope that makes sense?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[JPayne78]

Hi John thanks for your response, this works perfectly finding and matching the files from the payments and packs sheets with any already on the pending sheet and them moving those files onto the accepted sheet, so thanks for your help that will save me so much time. However, and sorry to be a pain, I also need any files on the payments and packs sheets that do not match files already on the pending sheet to then be added to the bottom of the pending sheet. Does that make sense and can this also be done?

Thanks in anticipation

[JOHN H. DAVIS]

Thought it would do that, try:

Please Login or Register  to view this content.