none
pastespecial method of range class failed

    Question

  • Sub text1()
    '
    ' text1 Macro
    '

    '
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    End Sub


    Excel 2007 sp3

    windows XP

    This was recorded using recoder in excel.

    Same code will work in other older .xlsm files but not in this new test files.

    Steps tried:

    Opened both files in same instance.

    Sample data in test1.xlsx.

    Open text2.xlsx > Start recording macro for paste>special> values and number formats>stop macro.

    Now try to copy data from text1.xlsx and highlight cell in text2.xlsx and try to run macro you get error.

    But works fine if i open the VB editor and use debug F8 step by step, But if i directly call for macro from Developer tab it doesnt and i get the pastespecial method of range class failed error.

    I believe its happening after installing office sp3.

    Is it an BUG. Tried to use the constant value 12 for xlPasteValuesAndNumberFormats, still same error.

     


    • Edited by Brightner Monday, December 12, 2011 7:32 AM Info update
    Monday, December 12, 2011 7:17 AM

Answers

  • Both files xlsm are closed, it calls the code from the third

    Sub Coping_test_from_closed_2xlsm()
    Application.ScreenUpdating = False
    Dim ws1 As Workbook, ws2 As Workbook
    Dim sciezka$: sciezka = "C:\Temp\" 'your path
     Workbooks.Open Filename:=(sciezka & "Zeszyt1.xlsm") 'change for your namefile
        Set ws1 = ActiveWorkbook
     Workbooks.Open Filename:=(sciezka & "Zeszyt2.xlsm") 'change for your 2nd namefile
        Set ws2 = ActiveWorkbook
    ws1.Sheets(1).Range("A1:B8").Copy 'any range adress copy
    ws2.Sheets("Arkusz1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False 'for purity
    ws1.Close False 'No save, we'r only copy
    ws2.Close True  'Save and exit
    Set ws1 = Nothing
    Set ws2 = Nothing
    Application.ScreenUpdating = True
    MsgBox "Now xlPasteValuesAndNumberFormats is et Zeszyt2.xlsm too", _
        vbInformation, "Data is coping well"
    End Sub
    

    Now if U must, you can modify the code, do you?


    Oskar Shon, Office System MVP

    Press if Helpful

    • Marked as answer by Brightner Monday, December 19, 2011 1:48 AM
    Tuesday, December 13, 2011 7:17 PM

All replies

  • Are you sure you missed nothing?

    Sub Coping_test()
    Dim ws1 As Workbook, ws2 As Workbook
    Set ws1 = Workbooks("test1.xlsx")
    Set ws2 = Workbooks("test2.xlsx")
    ws1.Sheets("Name_of_Source_worksheet").Range("l5").Copy 'any range adress
    ws2.Sheets("Name_of_Target_worksheet").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    Set ws1 = Nothing
    Set ws2 = Nothing
    End Sub
    



    Oskar Shon, Office System MVP

    Press if Helpful

    Monday, December 12, 2011 9:47 AM
  • On Mon, 12 Dec 2011 07:17:41 +0000, Brightner wrote:
     
    >
    >
    >Sub text1()
    >'
    >' text1 Macro
    >'
    >
    >'
    >    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    >        xlNone, SkipBlanks:=False, Transpose:=False
    >End Sub
    >
    >
    >Excel 2007 sp3
    >
    >windows XP
    >
    >This was recorded using recoder in excel.
    >
    >Same code will work in other older .xlsm files but not in this new test files.
    >
    >Steps tried:
    >
    >Opened both files in same instance.
    >
    >
    >Sample data in test1.xlsx.
    >
    >Open text2.xlsx > Start recording macro for paste>special> values and number formats>stop macro.
    >
    >Now try to copy data from text1.xlsx and highlight cell in text2.xlsx and try to run macro you get error.
    >
    >But works fine if i open the VB editor and use debug F8 step by step, But if i directly call for macro from Developer tab it doesnt and i get the pastespecial method of range class failed error.
    >
    >I believe its happening after installing office sp3.
    >
    >Is it an BUG. Tried to use the constant value 12 for xlPasteValuesAndNumberFormats, still same error.
    >
     
    Where is the macro located?  You cannot run macros from an xlsx file (by design), so your macro would need to be located within an xlsm or xlsb file.
     

    Ron
    Monday, December 12, 2011 12:55 PM
  • It is located within an XLSM file> Module.

    Sorry , Both are xlsm files.

    Ron you can try with sample test files and let me know the result please.

    Tuesday, December 13, 2011 3:55 AM
  • I actually wanted to know why this simple code is not working now as it was working before. Thanks for you workaround.
    Tuesday, December 13, 2011 4:58 AM
  • When I try to replicate your process as accurately as I can, I get erratic results.  Sometimes I get your error, and sometimes not.  The issue of problems with PasteSpecial when pasting between different workbooks has been noted before, and sometimes attributed to interference from some addin.  I don't believe that is the case here.

    I don't really have time this morning to look into this further, but I suspect the problem may have to do with either a security issue, or with the clipboard to which the command refers being empty at the time the PasteSpecial command is executed.  That could be empty either because you didn't copy anything before you ran the macro (the copy is not a part of your recorded macro), or due to some other issue having to do with the two files running in different instances of Excel, or the clipboard with the Copied information somehow not being accessible to the PasteSpecial.

    Oskar has apparently provided you with a working solution, where you execute the Copy from within the same macro, and that should continue to work.

    If I get a chance to look into this further, I will post back.


    Ron
    Tuesday, December 13, 2011 11:13 AM
  • I taught it should be an clipboard issue.

    But if i open the VB editor and check debug F8 it works fine. Not sure.

    Not even with new test files it working now.

    Thanks for you time and appreciate in taking so much of valuable time to reply to my post.

     

    Tuesday, December 13, 2011 12:46 PM
  • Both files xlsm are closed, it calls the code from the third

    Sub Coping_test_from_closed_2xlsm()
    Application.ScreenUpdating = False
    Dim ws1 As Workbook, ws2 As Workbook
    Dim sciezka$: sciezka = "C:\Temp\" 'your path
     Workbooks.Open Filename:=(sciezka & "Zeszyt1.xlsm") 'change for your namefile
        Set ws1 = ActiveWorkbook
     Workbooks.Open Filename:=(sciezka & "Zeszyt2.xlsm") 'change for your 2nd namefile
        Set ws2 = ActiveWorkbook
    ws1.Sheets(1).Range("A1:B8").Copy 'any range adress copy
    ws2.Sheets("Arkusz1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False 'for purity
    ws1.Close False 'No save, we'r only copy
    ws2.Close True  'Save and exit
    Set ws1 = Nothing
    Set ws2 = Nothing
    Application.ScreenUpdating = True
    MsgBox "Now xlPasteValuesAndNumberFormats is et Zeszyt2.xlsm too", _
        vbInformation, "Data is coping well"
    End Sub
    

    Now if U must, you can modify the code, do you?


    Oskar Shon, Office System MVP

    Press if Helpful

    • Marked as answer by Brightner Monday, December 19, 2011 1:48 AM
    Tuesday, December 13, 2011 7:17 PM
  • You can check the analysis done by Sheeloo

    http://answers.microsoft.com/en-us/office/forum/office_2007-excel/pastespecial-method-of-range-class-failed/fa77d1da-f1f6-4cc0-9eb1-f784ff783008?page=1&tm=1324273257489#footer

     

     

    Monday, December 19, 2011 5:47 AM