Copy formula to next available cell in row

[redspanna]

Hi all

Does anybody know how I can auto find the last cell populated in column B of my 'cars' sheet, then drag down and copy the formual into the next 7 rows.

for example if cell B65 holds the formula

=pivot!F649

this will be dragged down to populate B66:B72 where ....
B66 cell will hold formula =pivot!F650
B67 cell will hold formula =pivot!F651
B68 cell will hold formula =pivot!F652...etc etc

I guess the

With Sheets("cars").Range("B" & Rows.Count).End(xlUp).Copy

will find and copy the formula for me but to then drag it down 7 rows - I have no idea

any help much appreciated

 

[VoG]

Try

With Sheets("cars")
    .Range("B" & Rows.Count).End(xlUp).AutoFill Destination:=.Range("B" & Rows.Count).End(xlUp).Resize(8)
End With

 

[redspanna]

Thanks VoG

 

[redspanna]

..sorry just to add another question

If I wanted to expand the range so that after finding first availabe row in Column B the formula in BOTH columns B and E are dragged down 7 rows..

I though it might be

.Range("B:E" & Rows.Count).End(xlUp).AutoFill Destination:=.Range("B:E" & Rows.Count).End(xlUp).Resize(8)
End With

but it doesn't work
thanks again

 

[VoG]

Try

With Sheets("cars")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B" & LR).AutoFill Destination:=.Range("B" & LR).Resize(8)
    .Range("E" & LR).AutoFill Destination:=.Range("E" & LR).Resize(8)
End With

 

[redspanna]

Fab,.......many thanks !!