Macro to autofill formulas based on number in cell

[ml6845]

Hi,
I have a row of formulas and I want to be able to copy the formulas, go to the end of a range, paste the formulas, then drag down the formulas a certain number of rows (based on the number of days in a particular month, in cell A1), then copy and paste the formulas as values.

Here's what I have so far, only it currently does not autofill based on the number in A1 (it just pulls down the formulas 30 rows).

Sub Macro6()
'
' Macro6 Macro
''
Range("A3:K3").Select
Selection.Copy
Selection.End(xlDown).Select
Range("A8315").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A8315:K8345"), Type:=xlFillDefault
Range("A8315:K8345").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub



Thanks very much for any help!

 

[dermie_72]

when you say drag the formula, do you mean paste the formula in the last row, (or the first blank row), then increase the range by by the number in A1?

 

[ml6845]

when you say drag the formula, do you mean paste the formula in the last row, (or the first blank row), then increase the range by by the number in A1?

Yes exactly.

 

[dermie_72]

see if this works for you:

Sub ml6845()
'for http://www.mrexcel.com/forum/excel-questions/748937-macro-autofill-formulas-based-number-cell.html
Dim LR As Long
Dim i As Long
i = Range("A1").Value
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A3:K3").Copy Destination:=Range("A" & LR & ":K" & LR).Resize(i)
End Sub

 

[ml6845]

see if this works for you:

Sub ml6845()
'for http://www.mrexcel.com/forum/excel-questions/748937-macro-autofill-formulas-based-number-cell.html
Dim LR As Long
Dim i As Long
i = Range("A1").Value
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A3:K3").Copy Destination:=Range("A" & LR & ":K" & LR).Resize(i)
End Sub

Works great thanks!