Code to have cells copied from one sheet and pasted under cell in another sheet

hi! I have data in column a in sheet 1. there is a number in cell a1, a16, a31 and so on... the cells are evenly spiliy by 15 blank rows in between them

I would like to copy the cells I have in sheet 2 range a1:a14 and paste it under cell a2, then paste the same range (a1:a14) under cell a16 and so on... essencially taking the range from sheet 2 and pasting it under every cell with a number in it in sheet 1. there is a different number of cells with text in sheet 1 each time i would run the macro but the range that is to be copy and pasted from sheet 2 will be constant.

can anyone help me with this code!??!?! please please please

Test this.

Sub tst()
    Dim lRow As Long, i As Long
    lRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    For i = 1 To lRow Step 15
        Sheet1.Cells(i, 1).Offset(1).Resize(14).Value = Sheet2.Range("A1:A14").Value
    Next
End Sub

or testing for values each time (this stops at the first blank)

Sub copydown()
i = 1
While Cells(1 + 15 * i, 1).Value <> ""
Range("a1:a14").Copy Destination:=Cells(2 + 15 * i, 1)
i = i + 1
Wend
End Sub

is your sheet set up correctly? does "A16" have a value?
it worked for me and kept formulas.

maybe you want to upload your actual sheet.

1. You never said anything about formulas.

2.Loop works just fine for me.

Sub tst()
    Dim lRow As Long, i As Long
    lRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    For i = 1 To lRow Step 15
        Sheet2.Range("A1:A14").Copy Sheet1.Cells(i, 1).Offset(1)
    Next
End Sub

Check SheetCodeNames for match or provide example file so we have something to work with.

Please amend your previous post with Code Tags.

Glad you got it sorted out. Thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

How does this work for you ?

Sub zoo()
Dim lRow As Long, i As Long
lRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
For i = 1 To lRow Step 15
    Sheet2.Range("A2:A15").Copy
    Sheet1.Cells(i, 1).Offset(1).Resize(14).PasteSpecial xlPasteFormulas
    Sheet2.Range("B2:B15").Copy
    Sheet1.Cells(i, 2).Offset(1).Resize(14).PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
'2 represents which cell to paste it in
Next
End Sub

Originally Posted by fruitloop44

hi! I have data in column a in sheet 1. there is a number in cell a1, a16, a31 and so on... the cells are evenly spiliy by 15 blank rows in between them

I would like to copy the cells I have in sheet 2 range a1:a14 and paste it under cell a2, then paste the same range (a1:a14) under cell a16 and so on... essencially taking the range from sheet 2 and pasting it under every cell with a number in it in sheet 1. there is a different number of cells with text in sheet 1 each time i would run the macro but the range that is to be copy and pasted from sheet 2 will be constant.

can anyone help me with this code!??!?! please please please

You talk in range a1,a16 =number and in A2:A15.value=sheet2.[a1:a14]
Run code in sheet1

sub test ()
Sheet2.[a1:a14].copy
Dim r,r1 as range
Set r1=range("A1",[A100000].end(3)).specialcells(2,1).areas
For each r in r1
Msgbox r(r.count+1,1) & "this cell to paste"
r(r.count+1,1).PasteSpecial XLPasteFormulas
Next r
Application.cutCopyMode = 0
End sub