I apologize in advance for the wordiness and # of macros posted. This isn't an easy problem for me.
I have a worksheet used to build a forecast, with anywhere from ten to 80 columns. The columns are divided into series by month and type, so for instance I may have 4 columns titled "Base Projected April", "Base Projected May", Base Projected June", etc., followed by a single column called "Authorized", followed by 4 more named "Adjusted April", "Adjusted May", etc. The # of columns depends on when in the fiscal year (September through August) I'm doing the forecast. For instance, if I'm doing one in October, it would be for October through August (11 months) whereas if I did it in July it would only be for 2 months. After the initial population of the forecast, that number of forecasted columns would need to be replicated with new headings, and a different function in each series depending on what was needed. That's where it's getting complicated.
I have a few macros that can do parts of what I need to do, but I don't know how to string them together to do all of what I need.
The first one adds the number of columns needed for the first series
The next one adds the formulas I need in that first series:
Now I have an improved version of the first macro, which will add all of my column headings at one shot.
The problem with this one is that if I use it, my macro for completing the first series fills in the same formulas under all of the columns. That's not a huge deal, as I can overwrite what's in the columns, but I need to know how to paste my correct formulas under those column headings.
I've copied and tried to adapt another macro I found on the internet, which is supposed to paste formulas based on the column heading.
However, I need it to run in conjunction with my other macros, and it needs to be able to look at PART of the column heading to determine the formula. For instance, most of the columns will have a month name, but I won't know in advance what months will be used, so can only hard code part of the name.
I realize this is very confusing. I've attached my worksheet in the hopes that it will make clearer what I'm trying to accomplish. I've put samples of each of the formulas in the top row of the example, and started formatting it the way I want it to appear. Putting the boxes around each section also seems challenging to me.
Any help I can get with this is greatly appreciated. AFTER this (probably another post) I'll need how to do vlookups later to some of these columns. How I'll do that without knowing what the column is called seems tough from here. Maybe by then I'll figure it out.
Bookmarks