Excel VBA: Move Column Based on Header (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

While working with VBA in Excel, we often need to move one or more column (s) based on Header (s). In this article, I’ll show you how you can move a column based on the header in Excel VBA.


Excel VBA to Move Column Based on Header (Quick View)

Sub Move_Multiple_Columns()

SheetName = "Sheet2"
DataSet = "B3:E13"

Dim Headers() As Variant
ReDim Headers(2)
Headers = Array("Employee Name", "Home Country")

Output = "G3"

Worksheets(SheetName).Activate

For i = LBound(Headers) To UBound(Headers)
    For j = 1 To Range(DataSet).Columns.Count
        If Range(DataSet).Cells(1, j) = Headers(i) Then
            Range(DataSet).Range(Cells(1, j), Cells(Range(DataSet).Rows.Count, j)).Copy
            Range(Output).Cells(1, i + 1).PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False
            Exit For
        End If
    Next j
Next i

End Sub

VBA Code to Move Column Based on Header in Excel


Move Column Based on Header with Excel VBA: 3 Quick Methods

Here I’ve got a data set that contains the names, joining dates, salaries, and home countries of some employees of a company in the range B4:E13 of a worksheet.

Data Set to Move Column Based on Header in Excel VBA

Our objective today is to move one or more columns from this data set with Excel VBA.


1. Move a Single Column Based on Header in Excel VBA

First, we’ll learn to move a single column based on the header. For example, let’s try to move the column with the header “Employee Name” to cell G4 of the worksheet Sheet1.

The VBA code will be:

⧭ VBA Code:

Sub Move_Single_Column()

SheetName = "Sheet1"

DataSet = "B3:E13"

Header = "Employee Name"

Output = "G3"

Worksheets(SheetName).Activate

For i = 1 To Range(DataSet).Columns.Count
    If Range(DataSet).Cells(1, i) = Header Then
        Range(DataSet).Range(Cells(1, i), Cells(Range(DataSet).Rows.Count, i)).Copy
        Range(Output).PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
        Exit For
    End If
Next i

End Sub

VBA Code to Move Column Based on Header in Excel VBA

⧭ Output:

Run the code. It’ll move a copy of the column “Employee Name” to cell G4 of the worksheet.

⧭ Note:

The first 4 lines of the code contain the inputs to the code (Worksheet Name, Dataset Range, Header of the Column that’ll be moved, and the Output Cell). Don’t forget to change those according to your needs before running the code.


2. Move Multiple Columns Based on Header in Excel VBA

Now, we’ll learn to move multiple columns based on the headers. For example, let’s try to move the columns with the headers “Employee Name” and “Home Country” to cell G4 of the worksheet called Sheet2.

The VBA code will be:

⧭ VBA Code:

Sub Move_Multiple_Columns()

SheetName = "Sheet2"
DataSet = "B3:E13"

Dim Headers() As Variant
ReDim Headers(2)
Headers = Array("Employee Name", "Home Country")

Output = "G3"

Worksheets(SheetName).Activate

For i = LBound(Headers) To UBound(Headers)
    For j = 1 To Range(DataSet).Columns.Count
        If Range(DataSet).Cells(1, j) = Headers(i) Then
            Range(DataSet).Range(Cells(1, j), Cells(Range(DataSet).Rows.Count, j)).Copy
            Range(Output).Cells(1, i + 1).PasteSpecial Paste:=xlPasteAll
            Application.CutCopyMode = False
            Exit For
        End If
    Next j
Next i

End Sub

VBA Code to Move Column Based on Header in Excel

⧭ Output:

Run the code. It’ll move a copy of the columns “Employee Name” and “Home Country” to cell G4 of the worksheet.

to Move Column Based on Header in Excel VBA Output

⧭ Note:

This time, the first 6 lines of the code contain the inputs to the code (Worksheet Name, Dataset Range, Headers of the Columns that’ll be moved, and the Output Cell). Don’t forget to change those before running the code.

Read More: How to Move Columns in Excel Table


3. Move Column Based on Header with Criteria (Both Single and Multiple Columns)

Finally, we’ll learn to move single or multiple columns based on the headers with a criterion.

For example, let’s try to move the columns with the headers “Employee Name” and “Salaries” with salaries greater than or equal to $50,000.00 to cell G4 of the worksheet called Sheet3.

The VBA code will be:

⧭ VBA Code:

Sub Move_Multiple_Columns()

SheetName = "Sheet3"
DataSet = "B3:E13"

Dim Headers() As Variant
ReDim Headers(2)
Headers = Array("Employee Name", "Salary")

Criteria_Header = "Salary"

Output = "G3"

Worksheets(SheetName).Activate

For i = 1 To Range(DataSet).Columns.Count
    If Range(DataSet).Cells(1, i) = Criteria_Header Then
        Criteria_Column = i
        Exit For
    End If
Next i

For i = LBound(Headers) To UBound(Headers)
    For j = 1 To Range(DataSet).Columns.Count
        If Range(DataSet).Cells(1, j) = Headers(i) Then
            Range(DataSet).Cells(1, j).Copy
            Range(Output).Cells(1, i + 1).PasteSpecial Paste:=xlPasteAll
            Row_Number = 2
            For k = 2 To Range(DataSet).Rows.Count
                If Range(DataSet).Cells(k, Criteria_Column) >= 50000 Then
                    Range(DataSet).Cells(k, j).Copy
                    Range(Output).Cells(Row_Number, i + 1).PasteSpecial Paste:=xlPasteAll
                    Row_Number = Row_Number + 1
                    Application.CutCopyMode = False
                End If
            Next k
            Row_Number = 2
            Exit For
        End If
    Next j
Next i

End Sub

⧭ Output:

Run the code. It’ll move a copy of the cells of the columns “Employee Name” and “Salary” to cell G4 of the worksheet if the salary is greater than or equal to $50,000.

⧭ Note:

This time, the first 7 lines of the code contain the inputs to the code (Worksheet Name, Dataset Range, Headers of the Columns that’ll be moved, Header of the Column that Contains the Criteria, and the Output Cell). Don’t forget to change those before running the code.

Also, the 23rd line of the code contains the criterion (>=50,000). Change it according to your needs.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So these are the ways to move a column based on the header using VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles


<< Go Back to Move Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo