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

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


Download Practice Workbook

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


3 Methods to Move Column Based on Header in Excel VBA

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.

Read More: How to Move Columns in Excel (5 Quick Methods)


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 Multiple Columns in Excel (4 Quick Ways)


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 need.

Read More: Excel VBA: Cut and Insert Column (5 Examples)


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. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo