macro to insert 1 column with formulas everytime it run

The problem you're going to have is that if you leave the formulas in the columns, the current week column will updated its values even right up to the moment we run the macro. So I'm going to suggest that you use the formulas to collate the data and then immediately remove the formulas. This way when you run the macro, it gives you an unchanging picture of the moment you ran it.

Does that make sense?

Give this a try. Make sure the data on Sheet2 is ready to evaluate and click the button, it will add the new sections and static values.

If you want to leave the formulas, remove or comment out the

Please Login or Register  to view this content.

...section of the macro.

Go back and look at the Wk column in my uploaded sheet. To make incrementing Wks easy, I actually removed the alpha characters and formatted the cell with a custom number format and a formula. There's no actual text in that cell. The first Wk 1 cell is actually a formula. You could just do a COPY/PASTE of of my cell onto yours and it would take all the fun formatting with it.

Looking at your sheet, I think we can simplify down the formula insertion to one step, too. Since the sheet names match the column A values exactly, we can use an INDIRECT() formula for the match and just put them all in in one step.

Please Login or Register  to view this content.

I also corrected an error in the second half's column-targeting.