asked on

Excel 2010 macro to insert column

I am creating a macro that is supposed to insert a new column C, then copy the contents of a different column into this new column. It works fine when I create the macro, but then when I go test the macro, it does not actually insert the new column C, so the incorrect column is then copied into column C. Here is the macro code.

' test1 Macro
'

'
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWorkbook.Save
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
Range("C2").Select
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("C2:C9555").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("I2").Select
ActiveCell.FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"
Range("I2").Select
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

Thanks - Eric

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrewssd3

If you are interested, this code shows how to do the same thing in more idiomatic VBA - it's also much quicker as it does not rely on the selection to do its work:

Sub Easier()

    Dim rngFill As Excel.Range

    Set rngFill = ActiveSheet.Range("C2")
    rngFill.EntireColumn.Insert

    ' set rngFill back to col C and to 9554 rows long
    Set rngFill = rngFill.Offset(0, -1).Resize(9554, 1)
    
    ' set the formula in all the cells in the range
    rngFill.FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
    
    ' move it across to column I (ie 6 cols to the right) - it keeps its dimensions
    Set rngFill = rngFill.Offset(0, 6)
    ' set the formula in all the cells in the range
    rngFill.FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"

    ActiveWorkbook.Save

End Sub

Open in new window

SiddharthRout

ejpren, Is this what you are trying?

Option Explicit

Sub Sample()
    Dim lastRow As Long
    
    '~~> Change Sheet Name Here
    With Sheets("Sheet1")
        '~~> Insert a new column in "C"
        .Columns(3).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        
        '~~> Get the lastrow of C
        lastRow = .Range("C" & Rows.Count).End(xlUp).Row
        .Range("C2:C" & lastRow).FormulaR1C1 = "=IF((RC[3]=""""),RC[2],RC[3])"
        
        '~~> Get the lastrow of I
        lastRow = .Range("I" & Rows.Count).End(xlUp).Row
        .Range("I2:I" & lastRow).FormulaR1C1 = "=TRIM(CLEAN(RC[-1]))"
    End With
End Sub

Open in new window

Sid

ejpren

ASKER

Perfect!

ejpren

ASKER

andrewssd3: - both of your answers work. I will look at them and learn!

SiddharthRout: - your answer initially gave "Run-time error '9': Subscript out of range" debug shows it errored at : With Sheets("Sheet1"). Changed the code to the actual sheet name. Re-ran; it did not copy the values into column C, but I am sure we could get there eventually.

Thanks to all, and I hope to learn more!

SiddharthRout

>>>your answer initially gave "Run-time error '9': Subscript out of range" debug shows it errored at : With Sheets("Sheet1").

That is obvious. Isn't it? I left comments in the code ;)

>>> Re-ran; it did not copy the values into column C, but I am sure we could get there eventually.

It works fine for me. :)

Anyways, glad your query is solved...

Sid