Forum

Macro to create as ...
 
Notifications
Clear all

Macro to create as many columns as the month number

2 Posts
2 Users
0 Reactions
107 Views
(@morielhamui)
Posts: 25
Eminent Member
Topic starter
 

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?  

 
Posted : 29/06/2021 4:35 pm
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 02/07/2021 12:03 am
Share: