Macro to copy cells four rows apart without using select

Question

This code copies the entries from Sheet1!A2, Sheet1!B2, etc. and pastes them onto Sheet2 with 3 rows between each entry. I want to duplicate this code without using .select.

Option Explicit

Sub Copy_Paste()

Dim i As Integer
For i = 1 To 100
    Sheets("Sheet1").Select 
    Range("A2,B2,C2,D2,E2").Select 
    ActiveCell.Range(Cells(i, 1), Cells(i, 2)).Select
    Selection.Copy 
    Sheets("Sheet2").Select 
    Cells(((i - 1) * 4) + 1, 1).Select 
    ActiveSheet.Paste 
Next i

End Sub

This is what I have so far, but it is not working.

Option Explicit

Sub Copy_Paste()

Dim i As Integer
For i = 1 To 100
    Dim ws1 As Worksheet, rng As Range, act As Range
        Set ws1 = Worksheets("Data")
        Set rng = ActiveSheet.Range("A2,B2,C2,D2,E2")
        Set act = ActiveCell.Range(Cells(i, 1), Cells(i, 2))
    Selection.Copy
    Dim ws2 As Worksheet, rng2 As Range
        Set ws2 = Worksheets("Calculate")
        Set rng2 = Cells(((i - 1) * 4) + 1, 1)
    ActiveSheet.Paste
Next i

End Sub

Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Luuklag, Oct 10 '18 at 07:18

score 0 · Answer 1 · answered Oct 10 '18 at 05:25

I used this type of operation in one of my vba codes:

      'do copy from reference "Answers_Source" worksheet
    wb.Sheets("Answers_Source").Range("h1:z160").Copy

   'now paste the formulas into the student exam workbook
   wb2.Sheets("Answers").Range("h1:z160").Paste

So you can edit that to your situation.

score 0 · Answer 2 · answered Oct 10 '18 at 06:06

you could use Offset() property of Range object

Sub Copy_Paste()
    Dim i As Long
    For i = 1 To 100
        Sheets("Sheet1").Range("A2,B2").Offset(i - 1).Copy Destination:=Sheets("Sheet2").Range("A1:B1").Offset((i - 1) * 4)
    Next
End Sub

while if you only need paste values, then it's quicker:

Sub Copy_Paste_Values()
    Dim i As Long
    For i = 1 To 100
        Sheets("Sheet2").Range("A1:B1").Offset((i - 1) * 4).Value = Sheets("Sheet1").Range("A2,B2").Offset(i - 1).Value
    Next
End Sub

score 0 · Answer 3 · answered Oct 10 '18 at 06:27

You know you can just say something like "Range x values = Range y values":

ws2.Range("A1:B4").Value = ws1.Range("A1:B4").Value

If you can define your ranges using Range(Cells(1,1), Cells(4,2)) then I'm pretty sure you can do everything you want in one line

Macro to copy cells four rows apart without using select

3 Answers3