How to copy range and paste it with one cell apart

ilya49

New Member
Joined
Nov 24, 2011
Messages
9
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305
Hi,

welcome to the board

Try:
Code:
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
 

alvinwlh

Active Member
Joined
Feb 16, 2009
Messages
305
Hi,

Sorry, I think this is better

Code:
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

New Member
Joined
Nov 24, 2011
Messages
9
Alvin,
thanks for the code,

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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top