Macro copy formulas down a specific range

[spill-the-beans]

Hi all,

In my sheet called Data, cells Y2:BF2 contain formulas.

At the moment, I've been manually dragging the formulas to copy them down all my data. I only need to copy them down as far as the last cell with data in column V2, but where this last row is will keep changing.

I was wondering if there was a way of creating a macro to find out where the last cell was, then copy the formulas from Y2:BF2 down every row, stopping at that last cell?

 

[dannac]

You can try this

Save your sheet/workbook before trying.

Right click sheet name - View code
Copy and paste code below

Put cursor at beginning cell in col Y and run macro

Sub filldown()

    
    Dim aRng As Range
    Set aRng = Selection.CurrentRegion
        

aRng.AutoFill Destination:=Range(aRng, Cells(Cells(Rows.Count, "V").End(xlUp).Row, "Y"))

End Sub

 

[spill-the-beans]

So I tried to run the code and it comes up with a box saying "Run time error 1004, AutoFill method of Range class failed", then highlights the last line of code before End Sub. . .