Autofill Filldown issue VBA

[Kelpot86]

Hi,

I have 'Master' formulas in I1:Q1, all are formulas except col. O which has a static number that I wish to be copied down. The vba copies the 'master' formulas and pastes formulas into I9:Q9 to then populate to end of data based on data in col G.

Autofill is working well for the formulas but is filling the static number in incrementally e.g should be 6 on every row and is filling it as 6,7,8,9...). I have read that Filldown will stop this.

However, I am struggling to work out how to convert this vba code to Filldown. Also, if I am successful, will it still work with the formulas or will it just paste the same formula all the way down (e.g static formula =mid(A9,5,6) all the way down when I want the formula to move with the row = mid(A10,5,6), mid(A11,5,6) etc?

"Sub Copy_Formula()'
' Copy_Formula Macro
'
    Sheets("Deposit Accounting").Select
    Range("I1:Q1").Select
    Selection.Copy
    Range("I9:Q9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("I9:Q9" & Range("G" & Rows.count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
End Sub"

 

[Fluff]

Try

Sub Copy_Formula() '
' Copy_Formula Macro
'
    Sheets("Deposit Accounting").Select
    Range("I1:Q1").Copy Range("I9:Q9")
    Range("I9:Q" & Range("G" & Rows.Count).End(xlUp).Row).FillDown
End Sub

 

[Kelpot86]

Yes that worked perfectly. Thank you! Looks a lot more tidy than mine too.

 

[Fluff]

You're welcome & thanks for the feedback