1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 – Macro (newbie) - Move Columns bases on name question

Discussion in 'Business Applications' started by ethanedward, Oct 23, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. ethanedward

    ethanedward Thread Starter

    Joined:
    Oct 23, 2009
    Messages:
    4
    I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

    So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)

    Example

    Email | Phone | Last Name | First Name

    I would like to have a macro cut/paste entire rows and put in a specific order

    First name |Last Name| Phone| Email

    Any help would be great! Thank you!
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.

    Regards,
    Rollin
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Now that I think about it, as long as the headers and number of columns remains the same you should be able to record a macro to accomplish what you want. Just start the macro recorder and then select all the cells on the sheet including the headers. Next perform a data sort on all the cells from left to right using row 1 as your sort by criteria (to sort from left to right click the options button under sort)

    Once you have the data sorted in either descending or ascending order you can just move the columns in the order you want. The resulting code produced by the macro recorder should be good every time you run it since prior to moving the columns they would be sorted in the same order each time.

    Regards,
    Rollin
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    You could always use something like this as well...

    Code:
    Option Explicit
    
    Sub MoveColumns()
        Dim aCols() As Variant, z As Long, iColCnt As Long
        Dim rFind As Range, rLook As Range
        aCols = Array("First Name", "Last Name", "Phone", "Email")
        Set rLook = ActiveSheet.Range("1:1")
        For z = LBound(aCols) To UBound(aCols)
            Set rFind = rLook.Find(What:=aCols(z))
            If Not rFind Is Nothing Then
                If ActiveSheet.Columns(z + 1).Address <> rFind.EntireColumn.Address Then
                    rFind.EntireColumn.Cut
                    ActiveSheet.Columns(z + 1).Insert
                End If
            End If
        Next z
        Application.CutCopyMode = False
    End Sub
    HTH
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/871116

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice