Copy New Data from Workbook and add to bottom of sheet on another workbook

Good Day All,

I am running a report to update information over the last year. I plan on running this report maybe once a week, maybe longer in between. What I am trying to do is to take the items from the new report and compare them to the report I already have. If there is a new item, then I would like to copy the item over to the "Outstanding List".

In the attachment, I made a sample of each workbook. I want to compare column A in each workbook and if it doesn't exist in the "Outstanding list" to copy that row from the New report over to the next available row at the bottom of the Outstanding List.

Please let me know if you need any additional information.

Thank you.

I am sure someone else could write better code, but this works for now. Running the ...Mast workbook will ask you to open your weekly report.

Please Login or Register  to view this content.

Thank you Maniacb. This is pretty much what I was looking for.

Since I will be adding this to the middle of a longer script, and the files I will be referencing will already be open, I assume I could replace the "FileToOpen" line and just use a "Workbook.activate" command? Or is that line used to set the file to filter through?

I ask because I will have 12 files open that I will have to filter through. (They will be exports from another software, 1 for each month). I was just going to copy the same script but change the active workbook. Will it be that easy?

As for functionality, the code is exactly what I am trying to do.

Yes, you can straight forward replace the FileToOpen with WorkBook.Activate. That part of the code only selects a worksheet. Glad to hear it worked for you

Good Day Maniacb,

I was unable to edit the code to work appropriately. can you help me rescript it with both the master and the new file open. I will include the name of the workbook and worksheets I am working with.

MasWB = ABC_Report.XLSM
MasWS = Outstanding Lists
OpenWB = 01_JAN.XLSX
OpenWS = Sheet1

I tried to alter you code but received errors. When I cleared the errors, it didn't copy the information over.

Here is the updated code and updated workbook

Please Login or Register  to view this content.

Excellent.

Thank you Maniacb. I made similar changes, but I must have missed something when I edited the original suggestion. With that being said, the last solution worked as expected. I will expand to run for all my items. Thank you again.

Glad it worked for you!

Jumped the gun again.

noticed it copied over items that were already on the MasWB. Is there a way to ignore items that already are on the list?

Try this code

Please Login or Register  to view this content.

Good Day Maniacb,

The last hurdle is I need to count the rows in the MasWB so it pasts the new value at the bottom of the list. As it is written, it is pasting over other data (depending on the number of rows on the OpenWB.

As of right now, and my testing, I think after "I" solve this problem, it will be good to go.

I greatly appreciate your help on this.

Good Day,

Try this update

Please Login or Register  to view this content.

and to limit what is copied over to only items that do not exist in the MasWB?

Good Day maniacb,

After I fiddled a little bit more, it didn't recognize the reference numbers on the MasWB because on the OpenWB, the numbers are exported as text. After converted to numbers, the script worked as expected.

I think that officially solves my problem and I GREATLY Appreciate your time.

Thanks Again.