Jacqui,

DestRange = "C2:" & "J" & a
This line sets the variant variable DestRange to "B2:J64" -
this is a string. The Autofill method requires a Range
Object for the destination parameter, so you get an error.

The solution is to add a Range(...) property statement
around the text to turn the text into a range object like
this:
Selection.AutoFill Destination:=Range(DestRange),
Type:=xlFillDefault

Cheers,
Dave.
>-----Original Message-----
>I have searched this newsgroup and managed to come up
with
>the following code to define a dynamic range for the
>autofill function
>
>Range("B2:B31").Select
>
>Range(Selection, Selection.End(xlDown)).Select
>
>
>a = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
>DestRange = "C2:" & "J" & a
>
>
>Selection.AutoFill Destination:=(DestRange),
>Type:=xlFillDefault
>
>When I run this I get an error Autofill method of range
>class failed.
>The actual destination range that is produced is exactly
>the same as what I would type in ie.
>
>'Selection.AutoFill Destination:=Range("B2:J64"),
>Type:=xlFillDefault
>
>Can anyone explain why this does not work?
>
>Thanks
>
>Jacqui
>.
>

Cheers, saved my sanity :-)


>-----Original Message-----
>Jacqui,
>
>DestRange = "C2:" & "J" & a
>This line sets the variant variable DestRange
to "B2:J64" -
> this is a string. The Autofill method requires a Range
>Object for the destination parameter, so you get an error.
>
>The solution is to add a Range(...) property statement
>around the text to turn the text into a range object like
>this:
>Selection.AutoFill Destination:=Range(DestRange),
>Type:=xlFillDefault
>
>Cheers,
>Dave.
>>-----Original Message-----
>>I have searched this newsgroup and managed to come up
>with
>>the following code to define a dynamic range for the
>>autofill function
>>
>>Range("B2:B31").Select
>>
>>Range(Selection, Selection.End(xlDown)).Select
>>
>>
>>a = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
>>DestRange = "C2:" & "J" & a
>>
>>
>>Selection.AutoFill Destination:=(DestRange),
>>Type:=xlFillDefault
>>
>>When I run this I get an error Autofill method of range
>>class failed.
>>The actual destination range that is produced is exactly
>>the same as what I would type in ie.
>>
>>'Selection.AutoFill Destination:=Range("B2:J64"),
>>Type:=xlFillDefault
>>
>>Can anyone explain why this does not work?
>>
>>Thanks
>>
>>Jacqui
>>.
>>
>.
>