Copy and paste non-contiguous cells in same position

[Jeff131313]

I want to paste a group of non-contiguous cells, and paste the values elsewhere on the sheet while maintaining the same relative position. is this possible via macro somehow?

for example, in column A i want to copy A1,A2,A5,A9; and paste into D1,D2,D5,D9 in one step.

thanks in advance!

 

[Teeroy]

Jeff131313, I though shg's idea was worth expanding to make a more general solution (not restricted to a single row) with a mouse selection for the destination.
Since it worked I thought I'd post it.

Sub Jeff_2()
    Dim rArea As Range, rCopyDest As Range, rStart As Range
    Dim lCol As Long, lRow As Long
    
    Set rStart = Selection.Cells(1, 1)
    Set rCopyDest = Application.InputBox(prompt:="Select Output Cell", Type:=8)
    lCol = rCopyDest.Column - rStart.Column
    lRow = rCopyDest.Row - rStart.Row
    
    For Each rArea In Selection.Areas
        rArea.Copy rArea.Offset(lRow, lCol)
    Next rArea
End Sub

 

[girishkdesh]

Jeff131313, I though shg's idea was worth expanding to make a more general solution (not restricted to a single row) with a mouse selection for the destination.
Since it worked I thought I'd post it.

Sub Jeff_2()
    Dim rArea As Range, rCopyDest As Range, rStart As Range
    Dim lCol As Long, lRow As Long
    
    Set rStart = Selection.Cells(1, 1)
    Set rCopyDest = Application.InputBox(prompt:="Select Output Cell", Type:=8)
    lCol = rCopyDest.Column - rStart.Column
    lRow = rCopyDest.Row - rStart.Row
    
    For Each rArea In Selection.Areas
        rArea.Copy rArea.Offset(lRow, lCol)
    Next rArea
End Sub

Dear Jeff,
You are genius. I am not good at VB, but that code you have given, dear, it works!
I want to learn VB. Can you please guide me through?
I should learn at least for the sake of Excel functioning.
Please help me. Tell me where to start, what to look forward and how to proceed.
Thank you very much for the solution, though!
Please do write me at girishkdesh@gmail.com.

 

[MarioMagno]

OK ...

Sub Jeff()
    Dim rArea As Range
    Dim sCol As String

    sCol = InputBox("Enter the column letter(s) to paste to")
    
    For Each rArea In Selection.Areas
        rArea.Copy Intersect(rArea.EntireRow, Columns(sCol))
    Next rArea
End Sub

Thanks Jeff for your support.
I've a question: ould I paste only "value" without formatting by you VBS ? Is there any change to obtain it?