Insert Columns using VBA

[Gusington]

Good Morning All,

I'm new to this - so bear with me...



This is what I am trying to achieve.



If Cell N2 contains a number (say 12), I want to add 12columns after column EN with the formatting from column EN.



There is a formula in cell EN3 which is"=IF(EN$5>'Tender Summary'!$E$20,0,EM3+7)", I want the new columnsto contain the same formula, however, the cell references need to be relativeto the new column. (i.e. the formula in column EO to read "=IF(EO$5>'TenderSummary'!$E$20,0,EN$3+7)"

The following formula are also within column EN:



In Cell EN5: "=EM5+1"

In Cell EN33: "=SUM(EN6:EN32)"



Both of these formula also need to be relative to the newcolumns inserted.


Any help would be greatly appreciated.






Thank you very much in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have assumed that N2 will either be blank or contain a positive whole number.
If that is so, give this a try in a copy of your workbook.

Sub Insert_Columns()
  Dim NewCols As Long
  
  NewCols = Range("N2").Value
  If NewCols > 0 Then
    Columns("EO").Resize(, NewCols).Insert
    Columns("EN").Copy
    Columns("EO").Resize(, NewCols).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Intersect(Range("EN3,EN5,EN33").EntireRow, Columns("EN").Resize(, NewCols + 1)).FillRight
  End If
End Sub

 

[Gusington]

Welcome to the MrExcel board!

I have assumed that N2 will either be blank or contain a positive whole number.
If that is so, give this a try in a copy of your workbook.

Sub Insert_Columns()
  Dim NewCols As Long
  
  NewCols = Range("N2").Value
  If NewCols > 0 Then
    Columns("EO").Resize(, NewCols).Insert
    Columns("EN").Copy
    Columns("EO").Resize(, NewCols).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Intersect(Range("EN3,EN5,EN33").EntireRow, Columns("EN").Resize(, NewCols + 1)).FillRight
  End If
End Sub

Hi Peter,

That work almost faultlessly.

It is copying the formula in the required cells except cell EN3. This is part of a merged cell with cell EN4 - would this cause the problem?

Is there a way to get this macro to run automatically such that as soon as the value is input into cell N2, the new columns are added immediately?

Thanks again,

Gus

 

[Peter_SSs]

It is copying the formula in the required cells except cell EN3. This is part of a merged cell with cell EN4 - would this cause the problem?

Yes.
VBA & merged cells do not sit together very well.
However, I think we can manage it in this circumstance.

Is there a way to get this macro to run automatically such that as soon as the value is input into cell N2, the new columns are added immediately?

Yes.
Try the following Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim NewCols As Long
  
  If Not Intersect(Target, Range("N2")) Is Nothing Then
    NewCols = Range("N2").Value
    If NewCols > 0 Then
      Columns("EO").Resize(, NewCols).Insert
      Columns("EN").Copy
      Columns("EO").Resize(, NewCols).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Range("EN3:EN4").Resize(, NewCols + 1).FillRight
      Intersect(Range("EN5, EN33").EntireRow, Columns("EN").Resize(, NewCols + 1)).FillRight
    End If
  End If
End Sub

 

[Gusington]

I've unmerged the cells and this does appear to be the problem. When unmerged it works perfectly - is there a way to do this when cells EN3 and EN4 are still merged?

Thank again.

 

[Gusington]

Peter,

That has worked perfectly. Thank you very much.

All the best.

Gus

 

[Peter_SSs]

Peter,

That has worked perfectly. Thank you very much.

All the best.

Gus

You're welcome. Thanks for the confirmation.