Automating inserting columns

[Matthayter]

Afternoon all;

Hoping someone can help me with what im sure is a simple problem.

I have an excel SS that has roughly 100 columns populated with data. However I need to insert a column starting with column D and then automatically inserting a new column every other column after that. E.g insert a column in D, F, H, J and so on and so on

any help greatly appreciated as the though of doing it manually is mundane

Thanks
Matt

 

[spencer_time]

Re: Automatting inserting columna

I'm still learning, so I can't promise I can help you, but I need to understand if I am even to try.

Is there always data in row 1 (ie. if the macro sees a value in row 1 of a column after D, then insert a new column)?
If not, does the number of columns of data always stay the same? how many, and what columns contain data?
How often would you want this to happen? only once? every time a button is clicked? every time spreadsheet is opened? etc...

I'll do my best to help, but I don't want to spend my time on it if I misunderstand the end goal/requirements.

 

[Joe4]

Re: Automatting inserting columna

Try this:

Sub MyInsertMacro()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = 4 To 100 Step 2
        Columns(c).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

You will just need to updated "100" to reflect how far our you want to go.

 

[Matthayter]

Re: Automatting inserting columna

Try this:

Sub MyInsertMacro()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
    For c = 4 To 100 Step 2
        Columns(c).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

You will just need to updated "100" to reflect how far our you want to go.

Thank you that worked perfectly

Matthew

 

[Joe4]

Re: Automatting inserting columna

You are welcome.