VBA - Copying and Pasting the Formatting

[AmeliaBedelia]

I am having difficulty with some VBA code that copies a cell value ("P4") on one sheet ("Form") and pastes it into another cell on different sheet ( Last row in column A on "Tracking" Sheet). When it pastes the value, it shows on the Home>Number menu that it is formatted as a number, but a warning message appears in the cell (green triangle in top left of cell) stating that the "Number stored as Text". This is impacting its ability to use the value in formulas.

Is there a way to add something to the code below to get rid of this warning message or stop it from storing the number as text? I want it to show up as a number with no decimal points.

If Sheets("Form").Range("A4").Value = "Correction" Then
Set SourceRange = Sheets("Form").Range("P4")
Set DestSheet = Sheets("Tracking")
lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set DestRange = DestSheet.Range("A" & lr + 1)

Sheets("Tracking").Select
SourceRange.Copy
DestRange.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Paste:=xlPasteFormats,_
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=True
Application.CutCopyMode = False

 

[Rick Rothstein]

I am having difficulty with some VBA code that copies a cell value ("P4") on one sheet ("Form") and pastes it into another cell on different sheet ( Last row in column A on "Tracking" Sheet). When it pastes the value, it shows on the Home>Number menu that it is formatted as a number, but a warning message appears in the cell (green triangle in top left of cell) stating that the "Number stored as Text". This is impacting its ability to use the value in formulas.

Is there a way to add something to the code below to get rid of this warning message or stop it from storing the number as text? I want it to show up as a number with no decimal points.

If Sheets("Form").Range("A4").Value = "Correction" Then
Set SourceRange = Sheets("Form").Range("P4")
Set DestSheet = Sheets("Tracking")
lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set DestRange = DestSheet.Range("A" & lr + 1)

Sheets("Tracking").Select
SourceRange.Copy
DestRange.PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Paste:=xlPasteFormats,_
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=True
Application.CutCopyMode = False

Just out of curiosity, if you replace what I highlighted in red with the following, does it work the way you want...

SourceRange.Copy DestRange

 

[AmeliaBedelia]

No, unfortunately it does not. The cell it is copying from (P4) actually has a formula in it that calculates a number value. Changing the code as you indicated above copies over the formula.