Monthly Report Variance VBA Macro

[smartpat19]

Hi all,

Thank you in advance for your help, I greatly appreciate it!

Current Situation: Every month my team reports estimated monthly spend for every project (about 200 projects). The process is manual with copy and paste into a ‘Master File’ by multiple analysts. I have created a macro that opens all the project files and adds the monthly spend to a access table. This alone has saved a lot of time. The table is formatted like this:

Link to Excel file:
Access Data Summary.xlsx
Password: TLCzjfNQp4

Resource on Egnyte

Resource shared using Egnyte

brookfieldproperties.egnyte.com

Current Problem: The department head wants a summary of the variances between monthly reporting based on project. Currently, we have a lead analyst that looks at all the data and compares to the prior month’s report…manually. I know there is an automated solution.

My idea: This would be in excel. I can connect to access and have parameters that allow only two reports to pull. I would like the summary to include projects that only had differences by month and then by year, Shown below. If the project does not have any variance it would not show up in the list.

Let me know if you need any more clarification, I am not sure how to start the variance comparison.

Thank you and I hope you and your family are staying healthy and safe,
Patrick

 

[smartpat19]

@DanteAmor I hope you are doing well. Do you have a moment to look at this question?

Thank you