Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Selection.PasteSpecial

    Good day,

    I have a spreadsheet with a Macro.
    My code is as follows:
    Range("Z4:Z29").Select
    Selection.Copy
    Range("AC4").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    All fields Z4:Z29 and AC4 range are formatted as Number with 2 decimals yet after the macro runs and pastes the values from Z4:Z29 they appear to be formatted as text and a little block appears that says:
    "The number in this cell is formatted as text or preceded by an apostrophe"

    Please can you help?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Try this:
    Code:
    Range("Z4:Z29").Copy
    Range("AC4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

  3. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    That also doesn't work, same outcome as before unfortunately.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hmmm... are you sure that Z4:Z29 aren't numbers formatted as text too? It'd be great if you would zip and attach your workbook?

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Yes I am sure. I have even formatted the cells as numbers again just to make sure. I won't be able to zip and upload, network constraints I am afraid.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    To be honest, I'm not sure how that's happening then. All I can suggest is an alternative way of copying across the numbers and hope that it works for you...

    Code:
    With Range("AC4:AC29")
        .NumberFormat = Application.International(xlGeneralFormatName)
        .Value2 = Range("Z4:Z29").Value2
    End With

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •