Excel macro to move column

Question

Basically I'm looking to move a column for one position to another.

561  DISK_GROUP_003 0   545     1
561  Disk_Group_iS  95  84144   80210
561  DISK_GROUP_iS  99  26335   26304
1415    t1_200ea    93  8804    8203
1415    t2_30010k   35  59846   21121
1415    t3_1tb72k   19  184941  36590
1415    t3_3tb72k   86  258635  224328
5018    t1_200ea    98  9905    9802
5018    t2_30015k   89  39987   35986
5018    t2_60015k   67  59984   40700
5018    t3_1tb72k   89  87567   78807
5018    t3_2tb72k   84  94412   79620

I need to move the 3rd column to the end at the right.

This is what I have tried so far:

Sub moveColumn()

With ActiveSheet        
        Excel.Columns(3).Cut
        Excel.Columns(6).PasteSpecial
End With

End Sub

But this method doesn't work as it gets a runtime error '1004'.

Any help would be much appreciated.

Answer 1

For those wondering, it's possible to do this without replacing the contents of the destination column.

For example, to cut column B and insert it to the left of column F, you can use

Columns("B").Cut
Columns("F").Insert Shift:=xlToRight

You can also replace the named column headers with column indices, to taste (so Columns("B") becomes Columns(2))

Answer 2

Pastespecial doesn't work with Cut. You can do this:

Columns(3).Cut Range("F1")
Columns(3).Delete Shift:=xlToLeft 'if you want to delete the empty column

Answer 3

The problem with the other answers given is that the cut/paste technique uses the clipboard—overwriting whatever is in it, and making it impossible for the program to operate correctly if another program that also uses the clipboard is running (such as another instance of the same VBA project).

Instead, do this:

Application.CutCopyMode = False ' don't want an existing operation to interfere
Columns("F").Insert XlDirection.xlToRight
Columns("F").Value = Columns("B").Value ' this would be one greater if to the right of F
Columns("B").Delete

Just note that if there are references to the existing column, they will break and not be updated.