Move Columns Based on Header Value

Posted on 2011-04-28
Last Modified: 2012-05-11
I have an Excel sheet in which I need to rearrange columns based on a cell value within that column using a Macro. What I mean is, I need the columns in a specific order (thanks to Access). It seems that the Excel report keeps changing based on user preference. The only constant is the text in Row 1. It always have the same values from report to report. I have attached two spreadsheets, one is the template, the other is a different order. I just want to get #2 to look like #1. Of course, the row length will vary from report to report. Is this even possible? I was thinking some HLookups and VLookups in combination, but couldn't figure it out.

Thanks for any help you can give me.

 Sample1---Template.xls  Sample2-Out-Of-Order.xls
Question by:G Scott
    LVL 43

    Accepted Solution

    Try this macro
    Sub reordercol()
    colarr = Array("W/S Business Focus Area", "Location", "W/S Project Focus Area", "Project Leader", "Project Name", "Est. CC Date", "Act. CC Date", "Ann Hard", "Status", "Oct", "Nov", "Dec", "FQ1", "Jan", "Feb", "Mar", "FQ2", "Apr", "May", "Jun", "FQ3", "Jul", "Aug", "Sep", "FQ4", "FY FY 11", "Special Init.", "BU", "Proj #", "Control Complete", "Status")
    For i = 0 To UBound(colarr)
    wc = Range(Cells(1, i + 2), Cells(1, 33)).Find(colarr(i)).Column
    If wc <> i + 2 Then
    Cells(1, wc).EntireColumn.Cut
    Cells(1, i + 2).Insert shift:=xlToRight
    End If
    Next i
    End Sub

    Open in new window

    LVL 1

    Author Closing Comment

    by:G Scott
    Worked perfectly! Thanks ssagibh.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now