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
ejprenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
I'm not 100% clear what you're asking, but I think the problem may be that this a recorded macro, and when you started recording you had the whole of column C selected.  The first line of this macro inserts cells based on the current selection, so adding this line at the top may help:
Activesheet.Columns("C:C").Select

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andrewssd3Commented:
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

0
SiddharthRoutCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ejprenAuthor Commented:
Perfect!
0
ejprenAuthor Commented:
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!
0
SiddharthRoutCommented:
>>>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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.