Just like the Subject line says, I am trying to move whole columns around
based on the heading text. So, if heading text says "Accession number" I
want it to be in column E, and if it says X, I want it to be in column A.
Basically we will be getting files from somebody and the columns may not
always be aligned to the code I have already written, so I would like to have
code to look at the first row and based on what it says in the first row,
move whole columns in order without deleting any of the records. How can I
do this? Any help would be greatly appreciated. THANKS!

Instead of moving the columns I might recomend creating a new sheets and
copying the columns over in the proper order. That way if something goes
wrong you have not messed up your source data (I am always leary about
playing with the original data, because I have seen it messed up far too
often). Here is some code to create a new sheet and move the columns over in
the proper order...

Sub FormatData()
Dim wksCurrent As Worksheet
Dim wksNew As Worksheet
Dim rngHeadings As Range
Dim rngCurrent As Range

Set wksCurrent = ActiveSheet 'Could be any sheet you want
Set wksNew = Worksheets.Add
With wksCurrent 'Assume headings are in row 1
Set rngHeadings = .Range(.Range("A1"), .Cells(1,
Columns.Count).End(xlToLeft))
End With
For Each rngCurrent In rngHeadings
Select Case rngCurrent.Value
Case "This" 'Heading This Goes to C
rngCurrent.EntireColumn.Copy wksNew.Columns("C")
Case "That" 'Heading That Goes to B
rngCurrent.EntireColumn.Copy wksNew.Columns("B")
Case "The Other" 'Heading The Other Goes to A
rngCurrent.EntireColumn.Copy wksNew.Columns("A")
End Select
Next rngCurrent

End Sub
--
HTH...

Jim Thomlinson


"Brett Smith" wrote:

Just like the Subject line says, I am trying to move whole columns around
based on the heading text. So, if heading text says "Accession number" I
want it to be in column E, and if it says X, I want it to be in column A.
Basically we will be getting files from somebody and the columns may not
always be aligned to the code I have already written, so I would like to have
code to look at the first row and based on what it says in the first row,
move whole columns in order without deleting any of the records. How can I
do this? Any help would be greatly appreciated. THANKS!

Wow Jim, thanks. I'm gonna try this out and modify it to fit my needs.
Thanks, I really appreciate it.

Brett

"Jim Thomlinson" wrote:

Instead of moving the columns I might recomend creating a new sheets and
copying the columns over in the proper order. That way if something goes
wrong you have not messed up your source data (I am always leary about
playing with the original data, because I have seen it messed up far too
often). Here is some code to create a new sheet and move the columns over in
the proper order...

Sub FormatData()
Dim wksCurrent As Worksheet
Dim wksNew As Worksheet
Dim rngHeadings As Range
Dim rngCurrent As Range

Set wksCurrent = ActiveSheet 'Could be any sheet you want
Set wksNew = Worksheets.Add
With wksCurrent 'Assume headings are in row 1
Set rngHeadings = .Range(.Range("A1"), .Cells(1,
Columns.Count).End(xlToLeft))
End With
For Each rngCurrent In rngHeadings
Select Case rngCurrent.Value
Case "This" 'Heading This Goes to C
rngCurrent.EntireColumn.Copy wksNew.Columns("C")
Case "That" 'Heading That Goes to B
rngCurrent.EntireColumn.Copy wksNew.Columns("B")
Case "The Other" 'Heading The Other Goes to A
rngCurrent.EntireColumn.Copy wksNew.Columns("A")
End Select
Next rngCurrent

End Sub
--
HTH...

Jim Thomlinson


"Brett Smith" wrote:

Just like the Subject line says, I am trying to move whole columns around
based on the heading text. So, if heading text says "Accession number" I
want it to be in column E, and if it says X, I want it to be in column A.
Basically we will be getting files from somebody and the columns may not
always be aligned to the code I have already written, so I would like to have
code to look at the first row and based on what it says in the first row,
move whole columns in order without deleting any of the records. How can I
do this? Any help would be greatly appreciated. THANKS!