find last row..insert row with formaul

[redspanna]

Can anybody help in providing code to carry out the following

find last cell through column B
Insert a whole row after this cell
starting at cell Q1 go down to last populated cell
Copy formula Q:Y of that last populated row down one row

thanks in advance

 

[Andrew Poulsom]

Why do you need to insert a row if there is nothing below the last cell in column B? Is the last cell in columns B and Q on the same row?

 

[redspanna]

Ok..to explain further...

B:O is populated by the data from a userform
Q:Y holds formulas relating to the values in B:O

so example: last populated row in sheet is row 200

B200:O200 holds data from userform
Q200:Y200 holds formula relating to the values in B200:O200

Q202:Y202 holds formula (totals) for whole columns Q:Y

when the data from the userform is added through columns B:0 a search is carried out to find last populated cell in column B.

go across to cells Q:Y and insert a row, thus pushing the subtotals formulas down a line

the data is then pasted into columns B:O
the formula from last populated cell in rows Q:Y (starting from Q1 going downwards) is copied down to next row.

The end result meaning a whole new row of data id added – made up from the data inserted from the userform (B:O) and the formulas Q:Y AND TWO CLEAR ROWS REMAIN for the sub totals formulas Q:Y to be displayed

Hope that makes more sense

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LR As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("A" & LR + 1).EntireRow.Insert
    Range("Q" & LR & ":Y" & LR).Copy Range("Q" & LR + 1)
End Sub