Moving Column depending on Source

[Jojo97]

Hey everyone,

It's my first VBA project.

Target: I want to create a reporting tool which combines expected and actual revenues of online shops in one view.

I got two sources: "Expected Values" and "Actual Values". The third file is the target file I'm trying to achieve.

I want the Subtotal to move depending on the evolving "Actual Values". -> In the case we are in Week 5, we know the total revenues of week 1-4. Therefore I want them to be shown in the reporting tool. The Weeks 5-26 aren't known for sure know. But we died some calculations so we have "Expected values". I want them to be added to the list behind the Subtotal.

With every Week passing by I will update the "Actual Values" list. I want the Subtotal to move with the Weeks.

My steps for the solutions:

1. I need an If formula which gets whether we have actual values or not and copies them to the Reporting file (like in my added sheet).
2. I need a VBA which fills a column behind the last "Actual Value". I tried to connect it to the highest available week from the file "Actual Value". Yet I didn't get how to do that.
3. I need a VBA which fills the insertet column with =Sum(Week1-Max(Week))


Thanks for your help!!!

I'm a total noob in Makro