Copy formula/row range down to a value specified by another cell?

[kai.lau]

Hello all,

I am trying to copy a row of cells/formulas down x amount of times based on a cell value.

For example; Range ("A6:J6") x number of times (specified by a formula in cell "D3"-1), beneath "A6:J6". Can someone help? I have just started to learn about bits of VBA so don't quite fully understand destination ranges currently. Any help would be appreciated.

excel help.jpg

So A6:J6 needs to applied down to row A6+2:J6+2? Hope that makes sense.

[georgiboy]

Maybe something like:

Sub FillTo()

    Dim FillTo As Long
    
    FillTo = Range("D3").Value - 1
    
    Range("A6:J6").AutoFill Range("A6:J" & 6 + FillTo)


End Sub

Maybe someone else has something more efficient or another way round things..

Hope this helps

[mana]

Sub test()
     
    With Range("A6:J6")
        .Copy .Resize(Range("D3").Value)
    End With
       
End Sub

[kai.lau]

Sub test()
     
    With Range("A6:J6")
        .Copy .Resize(Range("D3").Value)
    End With
       
End Sub

works great! thanks for the swift reply

[kai.lau]

Maybe something like:

Sub FillTo()

    Dim FillTo As Long
    
    FillTo = Range("D3").Value - 1
    
    Range("A6:J6").AutoFill Range("A6:J" & 6 + FillTo)


End Sub

Maybe someone else has something more efficient or another way round things..

Hope this helps

thanks! works