How to copy and paste columns based on value in a cell.

[themagicman]

All, I need a VBA that will copy and paste columns based on a certain cells value. Please see attached file.

What I am needing is for a user to input the column that represents each header on the column order sheet. Then take the column listed there and copy that column on the input sheet and paste into the output sheet.

For example. If a user puts "A" in cell B2 on Column order, the macro will copy column A on input tab and paste to output tab. If the user put "C" in B2 it would copy column C from input and still paste to output column A.

 

[Chihiro]

Something like below.

Sub DemoCopy()
Dim colArr, i As Long
colArr = Sheets("Column order").Cells(1).CurrentRegion

For i = 2 To UBound(colArr, 1)
    With Sheets("Input")
        .Columns(Cells(1, colArr(i, 2)).Column).Copy Sheets("Output").Columns(i - 1)
    End With
Next
End Sub

Note: Currently code does not check if user has duplicate column(s) in Column B.

 

[themagicman]

Something like below.

Sub DemoCopy()
Dim colArr, i As Long
colArr = Sheets("Column order").Cells(1).CurrentRegion

For i = 2 To UBound(colArr, 1)
    With Sheets("Input")
        .Columns(Cells(1, colArr(i, 2)).Column).Copy Sheets("Output").Columns(i - 1)
    End With
Next
End Sub

Note: Currently code does not check if user has duplicate column(s) in Column B.

That worked great! Thank you!