Autofill range

[repairman615]

Hello,

I have some data set up in a table like fashion. (not a table) B7:L17.

I am working on a macro to insert a row at the bottom, format as above, and the fill the formulas down.

Sub a()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.AutoFill Destination:=[COLOR=red]Range(("B" & Range("B" & Rows.Count).End(xlUp).row) & ":L" & Range("B" & Rows.Count).End(xlUp).row + 1)[/COLOR], Type:=xlFillDefault
End Sub

the line in red give the problem. If I use range ("b7:b18") it is ok however since i would like to do this dynamic, that would not fill the new last line with the formulas above.

I won't be back till tonight. Thanks to all here.
Any insight is valued.

 

[Joe4]

I think the problem is you have the whole row highlighted and not columns B:L selected when you try to run the autofill.
Try this (note that I used a variable to "capture" the last row so I didn't have to keep re-calculating it):

Sub a()
 
    Dim myLastRow As Long
    myLastRow = Range("B" & Rows.Count).End(xlUp).Row
    Rows(myLastRow + 1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B" & myLastRow & ":L" & myLastRow).AutoFill Destination:=Range("B" & myLastRow & ":L" & myLastRow + 1), Type:=xlFillDefault
 
End Sub

 

[repairman615]

Joe4,

I would like to thank you for the code and especially for your thoughts and notes that you have provided. Very helpful.

As I am a VBA beginner (due to this website sparking my interest), I am very greatful for the 'fountain of knowledge' that is here.

Thanks again for taking the time to teach me something.

 

[Joe4]

Glad to help!
I love teaching people who want to learn!