AutoFill
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
.
|