Vba macro how to move columns

[gibsongk55]

Hi,

I have many spreadsheets that I need to check over and then rearrange the order of the columns while keeping the rows in the same order.

Is there a way I can do a vba macro to do this?

Here is what i need to do:

Move columns:
Q to A
R to B
B to C
H to D
G to E
I to F
J to G
K to H
L to I
M to J
O to K
N to L
C to M
F to N
P to O
E to P
D to Q

and then Delete column S

Thanks for any help,

Gibs

 

[Misca]

Try:

Sub ReArrangeColumns()
Dim i As Integer
Application.ScreenUpdating = False
With ActiveSheet
    For i = 1 To 17
        .Columns(Choose(i, 17, 18, 2, 8, 7, 9, 10, 11, 12, 13, 15, 14, 3, 6, 16, 5, 4)).Cut
        .Range("A1").Offset(0, i - 1).Insert Shift:=xlToRight
    Next i
        .Columns(19).Delete 'Deletes column S
End With
End Sub

 

[gibsongk55]

Hi,

Thanks for the reply. Only the first two columns came out in the correct order. The 3rd column originally column F moved to column C while it should have moved to column N.


Gibs

 

[mirabeau]

Would you like to test this code and see if it does OK for you.

Assumed initial layout is:

1. Columns to be moved around are on sheet1

2. In sheet2 down columnA are the column letters, Q, R, B etc that you want to move. In columnB are the column letters for the destination columns A, B, C etc. This code version assumes no headers in Cols1 and 2 on sheet2.

Sub movecols()
Dim a, n As Long, i As Long
Dim j As Long, lr As Long, n As Long
With Sheets("sheet2")
n = .Range("B1").End(4).Row
a = .Range("A1:B" & n)
For i = 1 To n
    For j = 1 To 50
        If Split(.Cells.Columns(j).Address, "$")(2) _
            = a(i, 1) Then a(i, 1) = j
        If Split(.Cells.Columns(j).Address, "$")(2) _
            = a(i, 2) Then a(i, 2) = j
    Next j
Next i
End With
lr = Sheets("sheet1").Cells.Find("*", after:=Cells(1), _
        searchorder:=xlByRows, searchdirection:=xlPrevious).Row
With Sheets("sheet1").Cells.Resize(lr)
    For i = 1 To n
        .Columns(a(i, 1)).Copy .Cells(1, a(i, 2) + 128)
    Next i
    For i = 1 To n
        .Columns(a(i, 2) + 128).Cut .Cells(1, a(i, 2))
    Next i
    .Columns("S").Delete xlToLeft
End With
End Sub

 

[Rick Rothstein]

Move columns:
Q to A
R to B
....
....

Just out of curiosity, is that column R the R before you moved Q to A or is it the R that results after you move Q to A (they are not the same)? I also have the same question for the rest of your moves.

 

[gibsongk55]

Hi,

Thanks for the help. I just tried that new code and I get "Duplicate declaration in current scope" error.

Highlighting this ", n As Long"

BTW there is a header row. But i don't think that matters on row 1.

Thanks,

Gibs

 

[gibsongk55]

Just out of curiosity, is that column R the R before you moved Q to A or is it the R that results after you move Q to A (they are not the same)? I also have the same question for the rest of your moves.

Umm not sure how to answer that. I think i need to read all the data and then move the columns from left to right if that is possible. Otherwise of course it will be overwriting something.

Gibs

 

[mirabeau]

Hi,

Thanks for the help. I just tried that new code and I get "Duplicate declaration in current scope" error.

Highlighting this ", n As Long"

BTW there is a header row. But i don't think that matters on row 1.

Thanks,

Gibs

Sorry about that.
Oversight by me.
Just delete one of the "n as long" (but keep commas correctly placed).

If you want to put in headers, very easily done.

 

[Rick Rothstein]

Umm not sure how to answer that. I think i need to read all the data and then move the columns from left to right if that is possible. Otherwise of course it will be overwriting something.

Gibs

Actually, I'm wrong about the R being different (because Q comes before R), but then next one where you move B to C will be affected by the prior moves.

 

[gibsongk55]

Sorry about that.
Oversight by me.
Just delete one of the "n as long" (but keep commas correctly placed).

If you want to put in headers, very easily done.

Okay I fixed that and ran it on a spreadsheet with 1,000 records. Took almost 2 minutes then gave me this error below.

run-time error' 1004:

Application-defined or object-defined error.

Highlighting this: ".Columns(a(i, 1)).Copy .Cells(1, a(i, 2) + 128)"

Thanks,

Gibs