Move Columns Based on Header Value

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
G ScottAsked:
Who is Participating?
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
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

G ScottAuthor Commented:
Worked perfectly! Thanks ssagibh.
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.

All Courses

From novice to tech pro — start learning today.