?
Solved

Formula for moving columns around

Posted on 2012-08-28
15
Medium Priority
?
334 Views
Last Modified: 2012-09-01
Hello Excel experts,

I have a bunch  of excel spreadsheets.   The columns are not in the order I want.

Is there a formula that will let me move columns from one location to another in one quick "instant"  rather than having to have to manually cut and paste copied column individually to a the new location?

Example.  I have a column "Z" that I want to insert before Column "B" and a column "T" that I want to insert to before Column "K" -- and so on.

For part 2 of this question, and I don't know if this is possible.  Let's say I have several excel spreadsheets in a Word folder.  It would be nice if the formula could apply to all of the spreadsheets in that folder.

All of the spreadsheets have the same columns with the same header names and all of the spreadsheets need to have the same columns moved from one column to the new positions as described in the first part of this question.  Again, this may not be possible, but thought I would ask.

Thanks!

Rowby
0
Comment
Question by:Rowby Goren
  • 9
  • 5
15 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38343605
If all of your spreadsheets need to be rearranged in the same manner, what you could do is record a macro. Go to the developer tab and select record macro. Now rearrange your columns as needed. When finished, go back to the devloper tab and select stop recording. Open your next spreadsheet, go to the developer tab and select macros. If you didn't give it a name it will show up as macro1. Select run and the macro will rearrange all the columns.

Flyster
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38343663
The order you move the columns will matter. As soon as you move column Z, what was Column K has become Column L. Here's a snippet of Visual Basic code to move column Z in front of Column B:
    Columns("Z:Z").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight

Open in new window

0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38343665
Sounds good.

That will work if need be.

Just want to check that there is no way to run a program that will rearrange the columns in all the spreadsheets in a windows folder.  

Rowby
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 38343668
Hi tdlewis

Thanks for that clarification.   Perhaps there is some visual basic (which is beyond my expertise) that will modify all of the spreadsheets in a windows folder?

Thanks!
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38343713
rowby, I doubt you will find anything off the shelf, but it's not a terribly complicated script to write. Here's what I would need to know:
1. What's the path to the directory?
2. What are the rules for moving the columns?
3. Do you want the files updated in place?

Note that it would be better to move the columns based on the content of row 1 rather than on the raw placement of the columns, but if there is no header row then rules like column Z before column B will work. In particular if you're updating in place, you don't want the script to be run twice and mess up the order of the columns.

Also, it would be helpful to have at least one sample file. If you don't want to attach it to this question, you can post it here:
http://www.exelana.com/upload.html

(I am the only person with access to files uploaded via that webpage.)
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38343773
Thanks.  I will get a sample file ready for you tomorrow....
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38346825
Hi tdlewis

I just uploaded the files to your server.

Thanks!

I will keep all posted here as the solution evolves!

Rowby
0
 
LVL 10

Assisted Solution

by:tdlewis
tdlewis earned 2000 total points
ID: 38348028
Here's the code that you need.
Const convertDir = "C:\convert"
Const updatedDir = "C:\convert\updated"

Sub ConvertDirectory()
Dim fileName As String
    
    fileName = Dir(convertDir & "\*.xls*")
    Do While fileName <> ""
        Workbooks.Open fileName:=convertDir & "\" & fileName
        ConvertFile ActiveWorkbook.Sheets(1)
        ActiveWorkbook.SaveAs fileName:=updatedDir & "\" & fileName, _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
        fileName = Dir
    Loop
End Sub

Private Sub ConvertFile(oSheet As Worksheet)
Dim lastRow As Long

    If oSheet.Cells(1, 1) <> "PREMIUM" Then
        oSheet.Columns("A:D").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        oSheet.Cells(1, 1) = "PREMIUM"
        oSheet.Cells(1, 2) = "DWELLING"
        oSheet.Cells(1, 3) = "Company"
        oSheet.Cells(1, 4) = "SEPARATE STRUCTURES"
    End If
    moveColumn oSheet, "Square Footage", 5
    moveColumn oSheet, "Site Zip Code", 6
    moveColumn oSheet, "Standard Use Code Description", 7
    moveColumn oSheet, "Number of Units", 8
    moveColumn oSheet, "Year Built", 9
    
    lastRow = oSheet.Range("J" & oSheet.Rows.Count).End(-4162).Row
    
    oSheet.Range("A2", "A" & lastRow).Cells.FormulaR1C1 = "=ROUNDUP(RC[1]*0.00269*85%+RC[1]*0.000945,0)"
    oSheet.Range("B2", "B" & lastRow).Cells.FormulaR1C1 = "=RC[3]*160"
    oSheet.Range("D2", "D" & lastRow).Cells.FormulaR1C1 = "=RC[-2]*0.01"
    
    oSheet.Columns("A").NumberFormat = "$#,##0"
    oSheet.Columns("B").NumberFormat = "$#,##0"
    oSheet.Columns("D").NumberFormat = "$#,##0"
    oSheet.Cells(2, 1).Select
End Sub

Private Sub moveColumn(oSheet As Worksheet, heading, destColumn)
    oSheet.Rows("1:1").Select
    Selection.Find(What:=heading, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If ActiveCell.Text <> heading Then
        MsgBox "The column '" & heading & "' is not present in the worksheet!"
        Exit Sub
    End If
    If ActiveCell.Column <> destColumn Then
        oSheet.Columns(ActiveCell.Column).Select
        Selection.Cut
        oSheet.Columns(destColumn).Select
        Selection.Insert Shift:=xlToRight
    End If
End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38348063
Hi  tdlewis,

Appreciate the code.  I am away from the office today but will try it out tomorrow.

I assume I import it in someway to Excell and then when I want to execute it I click on some button in Excel?  

Let me know the step(s) to  incorporate it into excel.

Thanks!!

Rowby
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38348111
Open the Visual Basic editor (ALT+F11) and paste that code into ThisWorkbook of some workbook. Save the workbook as "Excel Macro Enabled Workbook (*.xlsm). You can run the macro by choosing it from the macro list (ALT+F8). You can also have the macro invoked by a button or your Quick Access toolbar.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38352011
Hi

When I run it it ends with an error dialog box that says Object Variable or With block variable not set.

I used the Experts-exchange-before-for-experts-exchange.xlsm file that I got from you.

It almost seems to work.  It appears what's missing are the formulas.

:)

Thanks
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38352037
I uploaded to your server an example of the file after the macro was run.  It seemed to get the error before all of the columns were "cut: and paste" to the new location.
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 2000 total points
ID: 38352386
I updated moveColumn to handle missing columns:
Private Sub moveColumn(oSheet As Worksheet, heading, destColumn)
    oSheet.Rows("1:1").Select
    On Error GoTo notFound
    Selection.Find(What:=heading, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    If ActiveCell.Text <> heading Then
notFound:
        oSheet.Columns(destColumn).Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        oSheet.Cells(1, destColumn).Value = heading
        MsgBox "The column '" & heading & "' was not present in the worksheet!" & vbCr & _
            "A blank column was added to the worksheet."
        Exit Sub
    End If
    If ActiveCell.Column <> destColumn Then
        oSheet.Columns(ActiveCell.Column).Select
        Selection.Cut
        oSheet.Columns(destColumn).Select
        Selection.Insert Shift:=xlToRight
    End If
End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38352453
Hi  tdlewis,

Looks like the program is working fine.   Give me overnight to make sure I've got all that I need, but the results are looking great!

Rowby
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 38357247
Thanks!   It is working wonderfully!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question