Transferring data from two workbooks into one workbook

[excelnewbie444]

Hi,

I have no background in VBA but I recently learned that it is possible to transfer data from diff workbooks by just clicking one button.

Background: I have a daily tasks that involves copying data from two workbooks (dated yesterday, dated today) and copying them into a macro that compares them using the IF and VLOOKUP formulas. I manually copy the datas into the macro.

Worksheets have the same data format and naming convention, with diff dates.

Can anyone please help with the code? It would be a great help and would greatly minimize risks of human errors.

Microsoft Excel 2016
Windows 10

[#2 · Accepted solution]

I'd recommend using Power Query, it's perfect for this sort of thing.

Some articles that may help
https://www.youtube.com/watch?v=jDGJhcp4waQ
https://www.wiseowl.co.uk/blog/s2536/compare-query-editor.htm



Maybe the Inquire Addin may help too if it's purely a "What's changed" exercise
https://support.microsoft.com/en-us/office/compare-workbooks-using-spreadsheet-inquire-ebaf3d62-2af5...

[#3]

Thank you so much, this is very helpful. The youtube channel linked also has many helpful videos.

[#4]

Also, if you want to learn more on Power Query, I have videos here https://youtube.com/playlist?list=PLlHDyf8d156UFChHzgQIO2cdaNqOS8KX3

And I'd highly recommend fellow MVP Oz du Soleil's videos here https://www.youtube.com/c/OzduSoleilDATA/videos