So I've got a question. I am looking for a good way to dynamically search/copy/paste a list of columns to a new sheet, rearranging their order to the user's desires.
I found these 2 really good examples, but I still can't quite get it to what I need.
The first one errors out if the header is not found and also only pastes the output in the specified order.
The second one has a great error check and iterative loop, but still, if there header is not found, it moves to the next column and leaves a blank column in the middle of the output.
Question: How to get this second query to not have a blank column if the header is not found?
Bonus Question: How to add a dynamic selectable column header list? For example, to allow users to select different column headers rather than hardcoding it into the VBA? (like a Pivot Slice Filter?)
Copying by Column Header Name
--> assigns variable name to each Column Header to search for, and then finds the Column #
--> Then selects that Column # (not the column letter ex A:A) and pastes it into new sheet, one column at a time.
--> User can easily adjust column variables and can easily adjust output order of select/copy/paste columns
https://www.mrexcel.com/forum/excel...new-sheet-visual-basic-applications-code.html
--> code defines input and output names of sheets
--> code defines the array of header names to search for, this is also the order that the select/copy/paste will output in.
--> finds and copies the column, then pastes into output sheet
--> macro loops through entire list of arrays, placing each new select/copy/paste in next column over from last
--> macro has error check to just move on to next loop iteration if header not found
I found these 2 really good examples, but I still can't quite get it to what I need.
The first one errors out if the header is not found and also only pastes the output in the specified order.
The second one has a great error check and iterative loop, but still, if there header is not found, it moves to the next column and leaves a blank column in the middle of the output.
Question: How to get this second query to not have a blank column if the header is not found?
Bonus Question: How to add a dynamic selectable column header list? For example, to allow users to select different column headers rather than hardcoding it into the VBA? (like a Pivot Slice Filter?)
Code:
Sub MoveColumns() Dim wsO As Worksheet
Dim wsF As Worksheet
Dim i As Integer
Application.ScreenUpdating = False
Set wsO = Worksheets("Sheet4")
Set wsF = Worksheets("Sheet5")
myColumns = Array("First", "Last", "notexist", "ID#", "Salary")
With wsO.Range("A1:W1")
For i = 0 To UBound(myColumns)
On Error Resume Next
.Find(myColumns(i)).EntireColumn.Copy Destination:=wsF.Cells(1, i + 1)
Err.Clear
Next i
End With
Set wsO = Nothing
Set wsF = Nothing
Application.ScreenUpdating = True
End Sub
Copying by Column Header Name
--> assigns variable name to each Column Header to search for, and then finds the Column #
--> Then selects that Column # (not the column letter ex A:A) and pastes it into new sheet, one column at a time.
--> User can easily adjust column variables and can easily adjust output order of select/copy/paste columns
https://www.mrexcel.com/forum/excel...new-sheet-visual-basic-applications-code.html
--> code defines input and output names of sheets
--> code defines the array of header names to search for, this is also the order that the select/copy/paste will output in.
--> finds and copies the column, then pastes into output sheet
--> macro loops through entire list of arrays, placing each new select/copy/paste in next column over from last
--> macro has error check to just move on to next loop iteration if header not found
Last edited: