Excel Macro: Inserting a column and copying the formula into it from the adjacent column

Question

I am trying to insert a column into the sheet and copying the formulas into it from the adjacent column to the right.

The place to insert the column is being read from the work sheet itself. E.G Column S (Column 19).

So I need to insert a new Column at Column "S" and copy the formulas from the "Old" Column S, now Column T.

I am using the following code but it is giving me 1004 error.

 Sub Insert_Rows_Loop()
      Dim CurrentSheet As Object
      'MsgBox "ghj" & Sheet16.Range("H2").Value
      Sheet2.Cells(1, Sheet16.Range("H2").Value).EntireColumn.Select
      Selection.Copy
      Selection.Insert Shift:=xlToLeft
      Application.CutCopyMode = False

      Sheet2.Cells(1, Sheet16.Range("G2").Value).EntireColumn.Select
      Selection.Copy
      Selection.Insert Shift:=xlToLeft
      Application.CutCopyMode = False

      Sheet2.Cells(1, Sheet16.Range("F2").Value).EntireColumn.Select
      Selection.Copy
      Selection.Insert Shift:=xlToLeft
      Application.CutCopyMode = False
 End Sub

Welcome to Stackoverflow. Please not that this is not a code writing site where people request code to be developed for them. Waht you asking for is pretty simple. Try using the Macro recorder and inserting a `Column` before "S" and then copying the data across, then post that code here and see where we can help. This way you will avoid down votes for not having code with your question. Good Luck and enjoy Stackoverflow! — Jean-Pierre Oosthuizen, Mar 17 '16 at 11:15

score 0 · Accepted Answer · answered Mar 17 '16 at 12:19

The code below will do the following:

Insert a new Column to the left of current Column S
Set the Formulas in Column S to the Formulas in Column T (Old Column S) to Column S

  Dim CurrentSheet As Worksheet
  Set CurrentSheet = ThisWorkbook.Sheets("Sheet1")

 With CurrentSheet
      'Inserting the Column before Column S
      .Range("S1").EntireColumn.Insert
      'Copying the Formulas from the T(Old S) to S
      .Range("S1").EntireColumn.Formula = .Range("T1").EntireColumn.Formula
 End With

You need to adjust the value in the Range to suit your requirement and you Sheet Referencing will be different.

I hope you get the idea behind it.

score 0 · Answer 2 · edited May 23 '17 at 12:31

Your sample code is indicative of the problems that are encountered when relying upon .Select and .Activate methods to navigate and reference various cells and worksheets in a workbook. Avoid .Select and .Activate in favor of direct cell referencing¹.

The second issue is no predetermined order for the column insertion. While you can insert columns in a seemingly random order, if there is an ascending pattern to the column numbers the latter columns will change position when a column is previously inserted to the right of it. The 'best practise' is to insert the columns starting at the right and working toward column A.

The following has been based on what your sample code said as it does not follow the same flow as your narrative.

Sub Insert_Rows_Loop()
    Dim c As Long, arr As Variant

    With Sheet16
        ReDim arr(2)
        'get them in descending order so that inserting 1 does not change the position of 2 or 3
        arr(0) = Application.Large(.Range("F2:H2"), 1)
        arr(1) = Application.Large(.Range("F2:H2"), 2)
        arr(2) = Application.Large(.Range("F2:H2"), 3)
    End With

    With Sheet2
        For c = LBound(arr) To UBound(arr)
            With .Columns(arr(c))
                .Copy
                .Insert Shift:=xlToLeft
            End With
        Next c
    End With

    Application.CutCopyMode = False
 End Sub

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Excel Macro: Inserting a column and copying the formula into it from the adjacent column

2 Answers2