Rifat Hassan
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
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.
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
⧭ 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
⧭ Output:
Run the code. It’ll move a copy of the columns “Employee Name” and “Home Country” to cell G4 of the worksheet.
⧭ 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.
