Organizing Data by Column Headers

[loriann614]

Hello,
I am attempting to write code that will organize my data based on the column headers. I have looked for an answer on other threads and found some that were close to what I am looking for but not quite, and as I am new to VBA I don't feel confident in my ability to recognize what I should change to fit my needs. I am hoping someone on here can help me out.

To be more specific, my data is exported from an online site into a CSV file. The rest of the data cleaning/analysis process that I have designed macros for requires that the data be in a specific order - meaning, Column A has to be the data with the header "Coded Result", Column B has to be the data with the header "Date Specimen Collected", etc. When the file is exported from the site, it is possible that the columns will not be in the right order. I know one could of course manually move columns around to be in the right order, but my coworkers would prefer it to be an automated process, so I am trying to design a macro that will select columns based on their header and copy/paste them to the right placement; my thinking is the easiest way to do this is to have the macro find the column based on the header name, copy the whole column, and then paste it to a different sheet where I specify which column to use (ex. "find column with header 'Patient DOB' in sheet1, copy the whole column, and paste to sheet2 column H"). If you have a different idea of how to accomplish this, though, I am all ears.

Some details - there are 15 different columns, so the code will have to identify and organize 15 different headers; the column headers will remain the same every time the data is pulled, but the length of the columns will change.

I hope all that makes sense. I greatly appreciate any help you can provide.

 

[mumps]

What are the 15 headers and their corresponding columns?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Loriann()
    Dim ary As Variant
    Dim i As Long
    Dim fnd As Range
    
    ary = Array("Ward", "District", "County", "postcode")
    For i = 0 To UBound(ary)
        Set fnd = Range("1:1").Find(ary(i), , , xlWhole, , , False, , False)
        If Not fnd Is Nothing Then
            If fnd.Column <> i + 1 Then
                fnd.EntireColumn.Cut
                Columns(i + 1).Insert
            End If
        End If
    Next i
End Sub

Change the values in red to match you headers & add the other in the same manner.
They need to be in the order, you want the columns at the end of the macro

 

[loriann614]

Hi & welcome to MrExcel.
How about

Sub Loriann()
    Dim ary As Variant
    Dim i As Long
    Dim fnd As Range
   
    ary = Array("Ward", "District", "County", "postcode")
    For i = 0 To UBound(ary)
        Set fnd = Range("1:1").Find(ary(i), , , xlWhole, , , False, , False)
        If Not fnd Is Nothing Then
            If fnd.Column <> i + 1 Then
                fnd.EntireColumn.Cut
                Columns(i + 1).Insert
            End If
        End If
    Next i
End Sub

Change the values in red to match you headers & add the other in the same manner.
They need to be in the order, you want the columns at the end of the macro

Wow this works perfectly! Thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback