Automatically shift columns based on start/end date

Okay. Yes, please!

Hey Rflee,

How would you like the percentages shifted?
I see that you have a percentage in D2:M4 and 3 empty cells under the first date and the last two dates

Hi Carlmon,

Thanks for your reply. So the percentages are now filled according to the two dates. So from column D (2022-06) through column M (2023-03).

If I change the end date to 2023-05, the percentages should be filled from column D through column O. Additionally, if i change the start date to 2022-07, the percentages should start at column E.

Perfect I understand what you're saying. I'll get you back a code in 10 minutes

Here you are Rflee. If I helped you out, please consider adding rep. THX

Please Login or Register  to view this content.

Apologies, I forgot to remove the percentages if the date is before or after the selected times (fixed in the code below) .... Also, looks like you're new to VBA.. consider googling "How to write a code in VBA" to get to the proper location to run this code.

Please Login or Register  to view this content.

Thank you so much!

Will this also work if I add another project (Project B) underneath this one?

I could write a code that looks for a project in the left-most column and perform the same actions as above... attach an example workbook of that and I will get something for you.

I'm leaving my job soon and will get back to you on Monday if I leave before I finish.

I updated my workbook in the original post. I added a second tab where all the projects would be and their start/end-dates.

Thanks again and enjoy your weekend!

Will your start and end dates be the same for both projects?

No, they will be different for every project.

Hi sinter, thanks a lot! That makes more sense, indeed.

I forgot to mention, however: it should be possible to change the 100% capacity to, let's say, 80%. So it should be variable and the value should be manually adjustable.

It that possible?

Hi Rflee, Did you get this one resolved? I can write you something today if you still need help.

Thanks sintek, this is exactly what I'm looking for!

Hi carlmon, I got what I am looking for. Thanks to you too!

Hi sintek, may I ask for your assistence one last time?

I changed the year-month fields (YYYY-MM) from data type general to date, so they add up correctly, instead of going from 2022-12 to 2022-13.
But now the VBA module does not seem to work anymore. Did I break the whole thing now?

Also, is there an option to trigger the module with multiple actions? So not only if the '% Allocation' is entered, but also if the end date is changed?

If you have any time, it would be highly appreciated!

I can take a look while he's offline, can you post the latest workbook?

Thank you! I updated the file in my original post on the top, should be called 'UTA'. Can you see it?

Yep, looking at it now!

LOL I changed the offset from -3, -2 to -2, -1 and it also seemed to work.

I changed this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and it worked for me - but I also didn't finish checking that it was working entirely - it just had no errors.