VBA - Error in code

[jamescooper]

Hello, can anyone spot the obvious error in this code that is the reason for it not running please?

Private Sub Worksheet_Calculate()

Sheets("Data").Range("C" & Sheets("Data").Range("AD2")).AutoFill Destination:=Range("C" & Sheets("Data").Range("AD2") & ":C" & Sheets("Data").Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row)

End Sub

I am receiving runtime error 438. In cell AD2 the row is dynamic, but it is currently 2.

Thanks.

 

[jamescooper]

Sheets("Data").Range("C" & Sheets("Data").Range("AD2")).AutoFill Destination:=Sheets("Data").Range("C" & Sheets("Data").Range("AD2") & ":C" & Sheets("Data").Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row)

Altered slightly and still doesn't work :S

 

[Fluff]

Try

Private Sub Worksheet_Calculate()

Sheets("Data").Range("C" & Sheets("Data").Range("AD2")).AutoFill Destination:=Range("C" & Sheets("Data").Range("AD2") & ":C" & Sheets("Data").Range("D" & Rows.Count).End(xlUp).Row)

End Sub

 

[NoSparks]

try this

Private Sub Worksheet_Calculate()
    With Sheets("Data")
        .Range("C" & .Range("AD2")).AutoFill .Range("C" & .Range("AD2"), .Range("C" & .Range("D" & Rows.Count).End(xlUp).Row))
    End With
End Sub

 

[jamescooper]

try this

Private Sub Worksheet_Calculate()
    With Sheets("Data")
        .Range("C" & .Range("AD2")).AutoFill .Range("C" & .Range("AD2"), .Range("C" & .Range("D" & Rows.Count).End(xlUp).Row))
    End With
End Sub

Thanks.

Trying to make this autofill dynamic by adding 2 cell variables - any ideas what I am doing wrong?

With Sheets("Data")
.Range("A" & .Range("AD8") & ":" & "A" & .Range("AD10")).AutoFill.End(xlUp).Row
End With
Thanks.

 

[Fluff]

Before anything else, do either of the 2 suggestions, do what you originally asked for?

 

[jamescooper]

Yes, they do thank you.

 

[Fluff]

Ok, thanks for that.
For the modification, the code will be the same, except you need to change the column reference & also

Range("D" & Rows.Count).End(xlUp).Row)

needs to be replaced with

Range("AD10")

 

[jamescooper]

Thanks works nicely!

 

[Fluff]

Glad you sorted it & thanks for the feedback