• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Formula for moving columns around

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
Rowby Goren
Asked:
Rowby Goren
  • 9
  • 5
2 Solutions
 
FlysterCommented:
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
 
tdlewisCommented:
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
 
Rowby GorenAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Rowby GorenAuthor Commented:
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
 
tdlewisCommented:
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
 
Rowby GorenAuthor Commented:
Thanks.  I will get a sample file ready for you tomorrow....
0
 
Rowby GorenAuthor Commented:
Hi tdlewis

I just uploaded the files to your server.

Thanks!

I will keep all posted here as the solution evolves!

Rowby
0
 
tdlewisCommented:
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
 
Rowby GorenAuthor Commented:
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
 
tdlewisCommented:
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
 
Rowby GorenAuthor Commented:
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
 
Rowby GorenAuthor Commented:
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
 
tdlewisCommented:
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
 
Rowby GorenAuthor Commented:
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
 
Rowby GorenAuthor Commented:
Thanks!   It is working wonderfully!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now