Give the following code a try...

Range("A41:F41").Copy
Range("A40").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A40").Select

Note we didn't have to physically select the first range to copy it, nor the
second range cell to paste into it (although it looks like we do need to
select the range cell at the end to turn off the large selection).

Rick


"Oldjay" wrote in message
...

I have a row of formulas that yield a percentage. These formulas
reference
% values on another work sheet

I have the following Macro that copies these values to another row that is
formatted %

Private Sub CommandButton5_Click() 'Copies Calculated MI% to Actual MI%
Range("AB41:AF41").Select
Selection.Copy
Range("AB40").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("AB40").Select
End Sub

The copy results in numbers formatted as text

What am I doing wrong?

oldjay