Copy Column and Paste in different sheet starting at row 15 then every 87th row after

0
votes

I am trying to copy a column (Sheet 2, range("A6:A70") and paste in Sheet 1. I want the value in Sheet 2 A6 to paste in Sheet 1 A15, then the value in Sheet 2 A7 to paste 87 rows below A 15, and so on pasting the whole column(Sheet 2, A6:A70) every 87th cell in sheet 1.

I have tried different .offset commands and 

ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

but what i need the loop to do is

start at A15 and paste first value

then maybe:

Read where last paste was, move 87 rows down, paste next value, etc... 

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim c As Range

Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")

copySheet.Range("A6:A70").Copy

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(87, 0).PasteSpecial xlPasteValues

actual results are nothing - i am unsure what code i should be using.

1
excelvba

1 Answers

1
votes

Untested, but something like this perhaps:

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")

Dim i as Long
For i = 0 to 64
    pasteSheet.Range("A15").Offset(i * 87).Value = copySheet.Range("A6").Offset(i).Value
Next i

EDIT:

If you're dealing with merged cells in the pasteSheet, try

pasteSheet.Cells(15 + i*87, 1).Value = copySheet.Range("A6").Offset(i).Value