PDA

View Full Version : Copy formula/row range down to a value specified by another cell?



kai.lau
01-15-2018, 06:49 AM
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.

21353

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

georgiboy
01-15-2018, 07:39 AM
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
01-15-2018, 07:56 AM
Sub test()

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

End Sub

kai.lau
01-15-2018, 10:43 AM
Sub test()

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

End Sub


works great! thanks for the swift reply :)

kai.lau
01-15-2018, 10:44 AM
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 :)