copy formulae down one row

[liquid]

I have a data set in columns R to AJ which currently runs to row 170.

The last row (170 at the moment), is formula driven, and looks into another sheet.

Each day I want to copy the final row of data; paste the formulae into the next row down (so for today it would be copying Range R170: AJ170 and pasting into Range R171:AJ171). Then I want to go back into row 170 and paste over the cells with the values.

The result will be that as my data set grows, only the last row will be formula driven, with all the hostoric data being just values.

Anybody know a simple macro that will achieve this for me??

Thanks in advance.

 

[sweater_vests_rock]

liquid.

i think this should do the trick:

Sub CopyLastRow()

    Dim rngCopy As Range, rngLastRow As Range

'   Create copy range from last row in column R & used columns R:AJ
    Set rngLastRow = Range("R" & Rows.Count).End(xlUp).EntireRow
    Set rngCopy = Intersect(Range("R:AJ"), rngLastRow)
    
'   Copy and paste last row, convert old last row to values
    With rngCopy
        .Copy
        .Offset(1, 0).PasteSpecial xlPasteAll
        .PasteSpecial xlPasteValues
    End With
    
'   Empty object variables
    Set rngCopy = Nothing
    Set rngLastRow = Nothing
    
End Sub

cheers. ben.

 

[liquid]

you're a star.

Thanks a mill, was wracking my little brain on that and was very close, but just couldn't get it to work

cheers