Dear all,

As the title says, I'm having troubles building a macro which updates the progress of a current project.
I've got a sheet with a waterfall-like detailed planning of the project, which shows the progress (in %) of the project streams.

What I'm building is a function to take this progress percentage (updates per day) and copy it in another table (different sheet) which is linked to a chart, graphically showing the progress.
The copying part is all alright and I can perform the macro with a button, but now my problem is that it is not copying & pasting the right progress made in terms of the date.
What I need, in short, is to upgrade my macro to copy the progress percentage and paste it in the cell resembling the correct date (= today).

This is my current code:
Sub ProgressUpdate() 
    Dim Ans As VbMsgBoxResult
    Ans = MsgBox("Updating is needed only once a day. Did you already update today?", vbYesNo + vbQuestion)
    If Ans = vbYes Then Exit Sub

    Sheets("Sheet2").Range("C3").Copy
    Sheets("Sheet1").Range("B5").End(xlToRight).Offset(, 1).PasteSpecial xlValues
    Application.CutCopyMode = False
    
    Sheets("Sheet2").Range("C4").Copy
    Sheets("Sheet1").Range("B7").End(xlToRight).Offset(, 1).PasteSpecial xlValues
    Application.CutCopyMode = False
    
    Sheets("Sheet2").Range("C5").Copy
    Sheets("Sheet1").Range("B9").End(xlToRight).Offset(, 1).PasteSpecial xlValues
    Application.CutCopyMode = False
End Sub
I know the code is very 'unwieldy', but up to now it did the trick (see the warning added in the button click event), since I am a VBA dummy.
By the way, due to confidentiality the code/file is a mock version.
Can you guys help me to upgrade the macro? Please regard the file for a simple version of the problem: MacroForHelp.xlsm

Cheers,
Lennart

PS: is it possible to update the progress chart without opening the excel/clicking the button? So updating happens 'under the water'?
PPS: If you forget to update the progress chart daily and for example update 4 days later, is it possible to include the missed days in the macro (which would be prevented if PS works out)