You don't say where to copy it to, so I guessed

[vba]

Public Sub CopyData()
Dim mpCell As Range
Dim i As Long

With Worksheets("Sheet2")

i = 1
For Each mpCell In Worksheets("Sheet1").Range("A1:A10")

If mpCell.Value <> "" Then

i = i + 1
.Cells(1, mpCell.Value).EntireColumn.Copy Worksheets("Sheet1").Cells(1, i)
End If
Next mpCell
End With
End Sub
[/vba]