Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA - Moving Columns.

Status
Not open for further replies.
exRP12Nuke

exRP12Nuke

Technical User
Oct 5, 2011
49
US
Good Morning,

I have a quick question on how to streamline some VBA for excel. This is a long repetitve chunk of code that I would like to minimize and I have had no luck with my knowledge.

I pull data off of our local intranet at work and it does not come off in the order that I need it to, this is the process to move the columns to the order that I need.

Any help would be greatly appreciated.

Thanks!

Code: 
Private Sub MoveColumns()
    'Rearranging columns to be in a useful order, not the disorganization that comes off of the intranet.
    Columns("AU:AU").Select
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("AW:AW").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Columns("Q:Q").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Columns("X:X").Select
    Selection.Cut
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Columns("AV:AV").Select
    Selection.Cut
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight
    Columns("V:V").Select
    Selection.Cut
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
    Columns("AE:AE").Select
    Selection.Cut
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight
    Columns("Q:Q").Select
    Selection.Cut
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight
    Columns("AB:AB").Select
    Selection.Cut
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight
    Columns("U:U").Select
    Selection.Cut
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight
    Columns("AI:AI").Select
    Selection.Cut
    Columns("Q:Q").Select
    Selection.Insert Shift:=xlToRight
    Columns("AD:AD").Select
    Selection.Cut
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight
    Columns("V:V").Select
    Selection.Cut
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight
    Columns("AB:AB").Select
    Selection.Cut
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight
    Columns("AE:AE").Select
    Selection.Cut
    Columns("U:U").Select
    Selection.Insert Shift:=xlToRight
    Columns("AG:AG").Select
    Selection.Cut
    Columns("V:V").Select
    Selection.Insert Shift:=xlToRight
    Columns("AA:AA").Select
    Selection.Cut
    Columns("W:W").Select
    Selection.Insert Shift:=xlToRight
    Columns("AL:AL").Select
    Selection.Cut
    Columns("X:X").Select
    Selection.Insert Shift:=xlToRight
    Columns("AV:AV").Select
    Selection.Cut
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight
    Columns("AG:AG").Select
    Selection.Cut
    Columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight
    Columns("AO:AO").Select
    Selection.Cut
    Columns("AA:AA").Select
    Selection.Insert Shift:=xlToRight
    Columns("AR:AR").Select
    Selection.Cut
    Columns("AB:AB").Select
    Selection.Insert Shift:=xlToRight
    Columns("AQ:AQ").Select
    Selection.Cut
    Columns("AC:AC").Select
    Selection.Insert Shift:=xlToRight
    Columns("AK:AK").Select
    Selection.Cut
    Columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight
    Columns("AG:AG").Select
    Selection.Cut
    Columns("AE:AE").Select
    Selection.Insert Shift:=xlToRight
    Columns("AG:AG").Select
    Selection.Cut
    Columns("AF:AF").Select
    Selection.Insert Shift:=xlToRight
    Columns("AL:AL").Select
    Selection.Cut
    Columns("AG:AG").Select
    Selection.Insert Shift:=xlToRight
    Columns("AT:AT").Select
    Selection.Cut
    Columns("AI:AI").Select
    Selection.Insert Shift:=xlToRight
    Columns("AL:AL").Select
    Selection.Cut
    Columns("AJ:AJ").Select
    Selection.Insert Shift:=xlToRight
    Columns("AN:AN").Select
    Selection.Cut
    Columns("AK:AK").Select
    Selection.Insert Shift:=xlToRight
    Columns("AO:AO").Select
    Selection.Cut
    Columns("AL:AL").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AN:AN").Select
    Selection.Insert Shift:=xlToRight
    Columns("AS:AS").Select
    Selection.Cut
    Columns("AP:AP").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AQ:AQ").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AR:AR").Select
    Selection.Insert Shift:=xlToRight
    Columns("AW:AW").Select
    Selection.Cut
    Columns("AS:AS").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AT:AT").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AU:AU").Select
    Selection.Insert Shift:=xlToRight
    Columns("AX:AX").Select
    Selection.Cut
    Columns("AV:AV").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.SmallScroll ToRight:=-25
    Range("A6").Select
End Sub
 
Sort by date Sort by votes
Hi,

How are you imporing the table?

What is the source?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
It is off of our work intranet, I believe that it is XML format. I just select all, copy, and paste into excel. I have spoke to our IT guys to try and get the to format it how I need it, but apparently they cannot arrange the columns in the order that I need.
 
Upvote 0 Downvote
What about this ?
Code: 
Private Sub MoveColumns()
    'Rearranging columns to be in a useful order, not the disorganization that comes off of the intranet.
    ori = Array("AU","AW","Q","X","AV","V","AE","Q","AB","U","AI","AD","V","AB","AE","AG","AA","AL","AV","AG","AO","AR","AQ","AK","AG","AG","AL","AT","AL","AN","AO","AX","AS","AX","AX","AW","AX","AX","AX")
    dst = Array("G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AI","AJ","AK","AL","AN","AP","AQ","AR","AS","AT","AU","AV")
    For i = LBound(ori) TO UBound(ori)
        Columns(ori(i)).Cut
        Columns(dst(i)).Insert Shift:=xlToRight
    Next
    ActiveWindow.SmallScroll ToRight:=-25
    Range("A6").Select
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote


you can SORT you columns horizontally in the Data > Sort wizard. Use an inserted row to NUMBER the columns in the order you wish and then sort.

Turn on your macro recorder to record the entire process and reuse.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thanks Skip, that works well.

Although, PHV I am interested in what you provided as well. When I enter your code it runs fine until it gets to LBound (and I am going to assume UBound), I get an error that says an array is expected, I am not sure what to do to fix that, any ideas?
 
Upvote 0 Downvote
The code I provided you works fine, as is, in my excel 2007 VBA ...
 
Upvote 0 Downvote
PHV,

I had my variables declared wrong, that worked great once I changed them to variants.

Thanks!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
3K
dhookom
dhookom
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
480
combo
combo
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
766
hermanlaksko
hermanlaksko
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
423
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
555
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top