Move Columns Based on Header Value

Posted on 2011-04-28
Medium Priority
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

Saqib Husain, Syed earned 2000 total points
ID: 35485551
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


Author Closing Comment

by:G Scott
ID: 35490032
Worked perfectly! Thanks ssagibh.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 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