Dynamic Find/Copy/Paste of Columns to new sheet w/ error check?

SURFER349

Board Regular
Joined
Feb 22, 2017
Messages
50
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?)

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:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top