Copying range to variable number of rows

I have a number of worksheets (Sheets 1 to 4) with formulas in A2:J2.

These formulas need to be copied down to however many rows are populated in
Column A of another Sheet called Data.

So, if I import 140 rows of data into the Data sheet and run the macro the
formulas in Sheets 1 to 4 get copied from row 2 down to row 140, if I import
200 rows the formulas get copied to row 200 etc.

I'm struggling with this - probably because I'm using a variable (number of
rows) in another sheet rather than the equivalent of a shift-end-down-right
command.

Any help would be much appreciated

Thank you

Hi nospaminlich

Try this

This example will filldown A2:J2 on "Sheet1" to row (row with last value
in Sheets "Data" in column A)

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
.Range("A2:J2").AutoFill Destination:=.Range("A2:J" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"nospaminlich" <[email protected]> wrote in message news:[email protected]...
>I have a number of worksheets (Sheets 1 to 4) with formulas in A2:J2.
>
> These formulas need to be copied down to however many rows are populated in
> Column A of another Sheet called Data.
>
> So, if I import 140 rows of data into the Data sheet and run the macro the
> formulas in Sheets 1 to 4 get copied from row 2 down to row 140, if I import
> 200 rows the formulas get copied to row 200 etc.
>
> I'm struggling with this - probably because I'm using a variable (number of
> rows) in another sheet rather than the equivalent of a shift-end-down-right
> command.
>
> Any help would be much appreciated
>
> Thank you

That's brilliant Ron. Many thanks.