Copying range to variable number of rows

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

That's brilliant Ron. Many thanks.