Macro to Copy Column

[Michael151]

Hello all,

I’m trying to write a macro that will copy a column, then change the header of the new column in row 1:

In the column labeled “New_Deal”, make a copy of this column by inserting a new column to the right, then change the header of the new column to “New_Deal_2”

Before sheet:

Row1 Col1 New_Deal
Row2 Col1 Col2
Row3 Col1 Col2
Row4 Col1 Col2
Row5 Col1 Col2

After Macro:

Row1 Col1 New_Deal New_Deal_2
Row2 Col1 Col2 Col2
Row3 Col1 Col2 Col2
Row4 Col1 Col2 Col2
Row5 Col1 Col2 Col2

The New_Deal column can be in any column letter, which is why I’d like to use the header in row 1 to identify.

Any help is most appreciated – thanks!

 

[Smitty]

Isn't this a rehash of this thread?

 

[Michael151]

Two separate functions actually, but similar.

I'm trying to write a macro that will make a copy of a column, then change the header of that new column.

The second function is similar, but instead of copying a column, I'm trying to simply fill the column with one specific word.

 

[stnkynts]

Try :

Option Explicit
Sub IWillNeverDoublePostAgain()
Dim c As Range
Dim Count As Integer
Count = 2
For Each c In Sheets("Sheet1").Range("1:1")
    If c.Value = "New_Deal" Then
        c.EntireColumn.Copy
        c.Offset(0, 1).Insert Shift:=xlToRight
        c.Offset(0, 1).Value = "New_Deal" & Count
        Count = Count + 1
    End If
Next c
Application.CutCopyMode = False
End Sub

 

[Michael151]

Ha ha clever sub name stnkynts...

This almost works perfectly. The only thing is that the macro changes the new column name to New_Deal2. Even if I try changing the name to something else, it adds a 2 at the end of the name. Any ideas?

 

[stnkynts]

Ahh you wanted it to be New_Deal_2 not New_Deal2 correct? Also the only reason i through the count in there was incase you had multiple instances of New_Deal in the columns. If this never happens then just remove all the counts and change the parenthesis to be exactly what u want.

Try changing this line:

        c.Offset(0, 1).Value = "New_Deal" & Count

To this:

        c.Offset(0, 1).Value = "New_Deal_" & Count

I am pretty sure you still want the new column to be shifted to the right and the title changed if this is incorrect let me know.

 

[Michael151]

I don't want it to have a 2 at the end (sorry for the confusion, I just read my original post).

Perhaps changing to a different name would work better. How about the new column simply be "market_right_id" without a 2 at the end.

Sorry for the back and forth - thanks for the help

 

[Michael151]

Actually, nevermind I was able to adjust the code myself.

A big thanks for all your help!