I know this is a really old post but I was wondering if anyone could show me how to modify this macro so that the user does not get a choice of where the destination of the column will be (specifically, I want the macro to automatically select cell A2 as the destination.
Thanks in advance!
Here is the code that I would like modified:
Sub Contact_List()
Dim v As Variant
Dim nCol As Long
Dim nRow As Long
Dim rOut As Range
Dim iCol As Long
On Error Resume Next
v = Application.InputBox("Select range to copy", Type:=8).Value
If IsEmpty(v) Then Exit Sub
nRow = UBound(v, 1)
nCol = UBound(v, 2)
Set rOut = Application.InputBox("Select destination", Type:=8).Resize (nRow, 1)
If rOut Is Nothing Then Exit Sub
For iCol = 1 To nCol
rOut.Value = WorksheetFunction.Index(v, 0, iCol)
Set rOut = rOut.Offset(nRow)
Next iCol
End Sub
|