autofill 1 row (kinda)

[bibbyd01]

A really easy one to start the day!

I have a VBA code that creates a row, but I want to autofill from the above row, but only a certain amount of columns (currently 3 to 60). I was just going to use a simple autofill function one cell at a time, then move it as it will still be very quick, but there's got to be an easier way fo doing it, and, more importantly, I can't figure out how to autofil to a cell below the current activecell.

When I record a macro it gives a range that I'm autofilling to, ie Selection.AutoFill Destination:=Range("B7:B8"), Type:=xlFillDefault
I tried to replce the range with offset, but this doesn't work.

I'm using excell 2003.

 

[Yard]

Never really used Autofill, but maybe:

Selection.AutoFill Destination:=Selection.Offset(1,0), Type:=xlFillDefault

#

Or is that what you've already tried? :-S

 

[bibbyd01]

I didn't try this, but it came up with an error

'autofill method of range class failed'

 

[Yard]

Maybe

Selection.AutoFill Destination:=Selection.Resize(2, Selection.Columns.Count), Type:=xlFillDefault

 

[bibbyd01]

Cool, that works excellently.

Now, why does it work? I gather the resize function allows you to expand the selection, so if I want to have a selectiont hat's 3 rows tall and 10 wide I would use resize(3, 10)? This uses selection.columns.count but I don't understand why?

Also, do you know how I could copy down multiple cells

 

[Yard]

if I want to have a selectiont hat's 3 rows tall and 10 wide I would use resize(3, 10)? This uses selection.columns.count but I don't understand why?

Because you are using teh Selection object, there is no fixed size for the range you are Autofilling. If you select a range 5 columns wide, but you have specified to fill to a range 10 columns wide, the Autofill will fail. Therefore, don't specify the number of columns - get it from the Selection object.

Also, do you know how I could copy down multiple cells

Erm, vague question = vague answer.

Yes.

 

[bibbyd01]

I've come up with some vba code, so I think all is good in the world once more!

What I want to do is use a range that's 1 row tall, but 6 columns wide, and autofill it into the row below. Do I have to select a range first, then use the same range in the autofill line?