Drag Formula Based on Dynamic Range

[joanna_sjw]

Hi! I'm facing an error for autofill formula. The formula is found in the last row of column Q to Y (e.g. 100). But the thing is, the last row is dynamic hence I have also name the lastrows accordingly so it will change whenever the macro is ran.
The formula has to be dragged to the last row of column A (e.g. 200). The last row of column A is also dynamic as i have datasets pasted in daily.
Not too sure if anyone is able to help me with this line which the debugger is showing me where the error is.

Selection.AutoFill Destination = sht.Range("Q" & lastrow, Range("Y" & lastrow2))

Below is the code that I currently have.

Sub Drag_Formulas()
Dim lastrow As Long
Dim lastrow2 As Long
Dim StartCell As Range
Dim EndCell As Range
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("DATA")
lastrow = sht.Range("Q" & Rows.Count).End(xlUp).Row
lastrow2 = sht.Range("A" & Rows.Count).End(xlUp).Row
Set StartCell = sht.Range("Q" & lastrow)
Set EndCell = sht.Range("Y" & lastrow2)

Range("Q1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination = sht.Range("Q" & lastrow, Range("Y" & lastrow2))
Range(Selection, Selection.End(xlDown)).Select

End Sub

Thanks in advance!

 

[Fluff]

Replace

Range("Q1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination = sht.Range("Q" & lastrow, Range("Y" & lastrow2))
Range(Selection, Selection.End(xlDown)).Select

with

   Range(startcell, endcell).FillDown

 

[joanna_sjw]

Replace

Range("Q1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination = sht.Range("Q" & lastrow, Range("Y" & lastrow2))
Range(Selection, Selection.End(xlDown)).Select

with

   Range(startcell, endcell).FillDown

Works like a charm! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.