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
.

.