Hiding column triggers macro to transfer value within the hidden column to static values

Hi Forum,

I have a sheet with weekly based inventory values for several items (simplified from the actual sheet). Every week a plugin will enter dynamic values from the current inventory based on a data set and enters the value as a function. I would like to make a macro that tranfers these value (or within a specified range) to static values. This is because we see errors in some very old functions (more than a year old) causing errors in our system. So we have a need of static values with the old data/hidden columns.

Can someone help me to create a concept or solution for this?

Thank you in advance, Rasmus