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!

 

[shg]

    Dim rArea As Range

    
    For Each rArea In Range("A1,A2,A5,A9").Areas
        rArea.Copy rArea.Offset(, 3)
    Next rArea

 

[Jeff131313]

i'm sorry but I am not extremely familiar with VBA code. could you please type the entire code for the above scenario, or help elaborate on the step by step details.

thank you in advance!

 

[shg]

Sub Jeff()
    Dim rArea As Range

    For Each rArea In Range("A1,A2,A5,A9").Areas
        rArea.Copy rArea.Offset(, 3)
    Next rArea
End Sub

 

[Jeff131313]

this macro only will work once, and assumes I only want to paste 3 colums to the right. do you know if it is possible to have a dynamic macro that will work if I select column A and want to paste into column T or any other column that may not only be an offset of 3 like you have in the macro?

 

[shg]

How would the macro know where you want to paste the values?

 

[Jeff131313]

I was thinking of copying the non-contiguous cells, then selecting the first cell i want to paste into, and using a macro button to paste from current selected cell. would that work?

 

[shg]

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

 

[Jeff131313]

that worked thank you!

 

[shg]

You're welcome.