How to copy range and paste it with one cell apart

[ilya49]

Hi All,

What i am trying to accomplish is to copy a range, say "D7:D9" and paste it into another worksheet, with one cell in between horizontally and one cell in between vertically.

For example, cell D7 would be copied into D7 on a different worksheet.
Cell D8 would be copied into D10 on a new worksheet,
cell D9 into D12,

and horizontally,
D7 into D7 on a new worksheet,
E7 into G7 on a new worksheet,
F7 into J7.

Could anyone please help me out how to write such code.

Thank you

 

[alvinwlh]

Hi,

welcome to the board

Try:

Sub Copy_paste()
    Dim A(0 To 2)
    Dim x, y As Long
    Dim start_range, to_range As Range
    Set start_range = Sheets("sheet1").Range("D7")
    Set to_range = Sheets("sheet2").Range("D7")
    new_y = UBound(A)
 
For y = 0 To UBound(A)
    A(y) = start_range.Offset(0, y).Value
Next
 
For y = 0 To UBound(A)
    to_range.Offset(0, y * 2).Value = A(y)
Next
 
End Sub

 

[ilya49]

Thank you, with some modifications, it worked like magic.

 

[alvinwlh]

Hi,

Sorry, I think this is better

Sub Copy_paste()
    Dim x, y, Numberofcells  As Long
    Dim start_range, to_range As Range
    Set start_range = Sheets("sheet1").Range("D7")
    Set to_range = Sheets("sheet2").Range("D7")
    
    Numberofcells = 4 'Change to Suit

For y = 0 To Numberofcells
    to_range.Offset(0, y * 2).Value = start_range.Offset(0, y).Value
Next
        
End Sub

 

[ilya49]

Alvin,
thanks for the code,

I modified it to copy the range D7:M9. It pastes it perfectly with one cell apart horizontally perfectly.

Sub Copy_paste2()
    Dim x, y, Numberofcells  As Long
    Dim start_range, to_range As Range
    
    
For vt = 7 To 10

    Set start_range = Sheets("sheet1").Range("D" & vt & "")
    Set to_range = Sheets("sheet2").Range("D" & vt & "")
    
    Numberofcells = 9 'Change to Suit

For y = 0 To Numberofcells
    to_range.Offset(0, y * 2).Value = start_range.Offset(0, y).Value
Next

Next vt



        
End Sub

I was wondering, how would I add pasting it with one cell apart vertically, one cell between all rows. Any Clues?

Thanks.

 

[alvinwlh]

WOW! Great.

It is now D7 to M10.

Have a nice day!