Attached is a macro we have that auto fills the columns with our vlook up and changes based on which file directory we want ie facility. It is currently designed with 12 monthly columns starting with the input dates. I want to to be dynamic and first populate as many columns for rows 7&8 based on the number month- ie April would be 4 columns. This would limit the data pulled to the CY, would allow for growth in the next month. Right now I need to manually add a column each month- ie I have a new template for each month of the year. Is there a way to add a step?
Hi Moriel,
Noticed that you have in column F the newest month, so I guess you always insert a new column in column F, right?
You can use the code below to insert a column in column F:
Dim Rng As Range: Set Rng = ActiveSheet.Range("F:F")
Rng.Copy
Rng.Insert Shift:=xlToRight
Application.CutCopyMode = False
Rng.Cells(8).Offset(0, -1).Value = Application.WorksheetFunction.EoMonth(Date, 0)
But...
Expanding columns is not a good design.
I know this is the most common workaround to avoid repeating the account rows, but a tabular structure will give you much more flexibility.
