Macro: Insert Columns based on Cell Value / Copy Formula Over

[cmcd29]

Hi,

I need help with a macro to insert 'x' amount of columns based on a cell value entered, and then copy formulas over to the right for all of the new columns created (should copy the entire columns formulas over, i.e. not a range within the column). It should also always insert columns - after column E and before column F. Example below.

Before

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Qty of Columns Needed:[/TD]
[TD]x [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

After
2 columns have been inserted in between the previous E and F and the formulas have been copied to the right.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Qty of Columns Needed:[/TD]
[TD]2 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Date4[/TD]
[TD]Date5[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

So far I've managed to insert columns based on the value entered but cannot figure out how to copy to the right / integrate the two.

Sub Insert_Columns()
'insert columns based on cell value
On Error Resume Next

Columns("F").Resize(, Range("B1").Value).Insert
Columns("G").Resize(1, 2).FillRight

On Error GoTo 0

End Sub

Any help would be greatly appreciated!

Thanks,
Cassie

 

[bhos123]

Sub Insert_Columns()
Dim x As Long
x = Range("B2").Value

Columns("F:F").Select
Selection.Copy
Range(Cells(, 7), Cells(, 7 + x - 1)).Select
Selection.Insert Shift:=xlToRight


End Sub

 

[cmcd29]

Hi bhos123,

Thanks for replying so quickly. I've tried the macro but am getting Run-time error '1004'. When I debug it is highlighting Selection.Insert Shift:=xlToRight.

Cassie

 

[bhos123]

must be something to do with the formulas, do you have formulas(of Column F) depend on the columns right to F?

 

[cmcd29]

I'm not sure what the problem is. Columns F and all to the right of F are completely independent (they just contain data, no formulas). Columns C,D,E however do have formulas and they are all linked to one another (i.e. Column C is a date, then Column D is this date +1 and so forth).

 

[bhos123]

is the value in B2 is integer? if yes then it should run without errors.

 

[cmcd29]

The input value, is an integer, and is in B1, however when I input the code the first time I changed B2 to the correct B1. I've tried using with B2 also but still get the same error code unfortunately.

Sub Insert_Columns()
Dim x As Long
x = Range("B1").Value

Columns("F:F").Select
Selection.Copy
Range(Cells(, 7), Cells(, 7 + x - 1)).Select
Selection.Insert Shift:=xlToRight


End Sub

 

[bhos123]

surprising..it is running perfectly in my system.

 

[cmcd29]

I've managed to get the code working in a new workbook however it's only inserting / copying 2 columns every time rather than going by the number input in cell B1. Sorry about this - and thanks for your patience!

 

[bhos123]

Hope this has got sorted out