numbers change to text
 

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

numbers change to text
 

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

numbers change to text
 

I tried it got with the same result!!

"Rick Rothstein (MVP - VB)" wrote:

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

numbers change to text
 

I tried it with the same result

"Rick Rothstein (MVP - VB)" wrote:

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

numbers change to text
 

You are still getting the values pasted as text? Using the exact code I
posted (corrected for the range change I made when testing)? If yes, post
the formula you have in AB41 and AC41 along with the values in whatever
cells they are referencing. Also, which cells are formatted as percentage...
the cells being referenced on the other work sheet and/or those in AB41 to
AF41?

Rick


"Oldjay" wrote in message
...
I tried it got with the same result!!

"Rick Rothstein (MVP - VB)" wrote:

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

numbers change to text
 

Sorry for the delay in answering - my service provider was down

Cells are still being copied as text and I did correct the cells addresses

AB41 - =MAIN!205
AC41 - =MAIN!206
BOTH CELLS FORMATED AS %
MAIN!O205 & O206 FORMATED AS %


"Rick Rothstein (MVP - VB)" wrote:

You are still getting the values pasted as text? Using the exact code I
posted (corrected for the range change I made when testing)? If yes, post
the formula you have in AB41 and AC41 along with the values in whatever
cells they are referencing. Also, which cells are formatted as percentage...
the cells being referenced on the other work sheet and/or those in AB41 to
AF41?

Rick


"Oldjay" wrote in message
...
I tried it got with the same result!!

"Rick Rothstein (MVP - VB)" wrote:

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

numbers change to text
 

an added note - I formatted a cell on the Main sheet as %, then put 54 in the
cell
I then formatted the cell M105 on the Input sheet as % and then inserted
the formula =Main!M105. I then tried to Copy- Paste Special-Values to another
cell with the same result "Number formatted as text"

"Oldjay" wrote:

Sorry for the delay in answering - my service provider was down

Cells are still being copied as text and I did correct the cells addresses

AB41 - =MAIN!205
AC41 - =MAIN!206
BOTH CELLS FORMATED AS %
MAIN!O205 & O206 FORMATED AS %


"Rick Rothstein (MVP - VB)" wrote:

You are still getting the values pasted as text? Using the exact code I
posted (corrected for the range change I made when testing)? If yes, post
the formula you have in AB41 and AC41 along with the values in whatever
cells they are referencing. Also, which cells are formatted as percentage...
the cells being referenced on the other work sheet and/or those in AB41 to
AF41?

Rick


"Oldjay" wrote in message
...
I tried it got with the same result!!

"Rick Rothstein (MVP - VB)" wrote:

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

numbers change to text
 

every thing cleared up after I Repaired Excel
Thanks for the help

"Rick Rothstein (MVP - VB)" wrote:

You are still getting the values pasted as text? Using the exact code I
posted (corrected for the range change I made when testing)? If yes, post
the formula you have in AB41 and AC41 along with the values in whatever
cells they are referencing. Also, which cells are formatted as percentage...
the cells being referenced on the other work sheet and/or those in AB41 to
AF41?

Rick


"Oldjay" wrote in message
...
I tried it got with the same result!!

"Rick Rothstein (MVP - VB)" wrote:

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