Formula To Copy Cell Values To Another Sheet Not Formulas

[JLouis]

I have this piece of VBA code to copy between sheets but it copies the formulas and not the values. I've used the paste special via the recorder but Excel goes to sheet and I don't want to use screenupdating = false. Plus, this is much neater.

Can this formula be modified to copy values instead of formulas?

 Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

Thanks.

 

[Fluff]

How about

 Sheets("Sheet2").Cells(Rows.count, 2).End(xlUp).Offset(1, 0).Resize(, 7).Value = Sheets("Sheet1").Range("B2:B8").Value