Excel Macro/VBA to generate automated report.

Question

Hi everyone.  I am very new to VBA/Macros in Excel.  I was given a project at work.  We receive two excel reports each day (one in the morning and one in the evening) that have information from two separate systems.  We can report to those as Report1 (morning) and Report2(evening).  Now, we have a separate spreadsheet with a bunch of VLookups.  Assuming it is the morning recon) we currently manually copy yesterday's report info into a tab call "Prior" and then current day's report info to a tab labeled "Current".  The spreadsheet will run a recon between the two and display the differences in a tab labeled "Changes".  I was hoping to automate this all so the spreadsheet finds the file with yesterday's date and the file with today's date (both in a share drive) and automatically runs the copy/paste process and displays the "Changes" tab automatically.  There is more I need to do but, thought I would just start with that first.

Answer 1

Hi,

Using Macro/VBA scripts to generate an automated report in Excel is more complex than what's typically handled in this forum. It would be best if you could post your this on Excel IT Pro category of TechNet forums for assistance from IT professionals who specialize in Excel.

Regards.

Answer 2

Lyron is correct about VBA but your request may not need it.

...we currently manually copy yesterday's report info into a tab call "Prior" ...

PowerQuery can import multiple files into one workbook. If we have older versions of XL (prior to XL2010) we can use MS Query. Neither require VBA. As a first step, try to eliminate the copy/paste with importing using either of these two methods.  The next step will be automating selecting the proper file.  How we handle that depends on whether we use PowerQuery or MS Query.