I have the following code which does what I want it do apart from it copies and pastes row 8 each time instead of the one above where the formula has been copied down from:
Sheets("Data").Select LastRow = Range("C" & Rows.Count).End(xlUp).Row With Range("C8:L" & LastRow) .Copy .Offset(1) .Value = .Value .Offset(-1, 1)(1).Select End With