[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

Error code "PasteSpecial Method of range class failed"

I've been using this code for over a year now and all of a sudden I'm getting an error: "PasteSpecial method of range class failed". I haven't touched the code whatsoever so why would it all of a sudden start failing? It errors out every time now at the ".PasteSpecial xlPasteValues" line of my code.
Sub CopyFromNotInvoicedWB(strSourceWB As String, _
    strSourceWS As String, strSourceRange As String, _
    rngTarget As Range)
' copies information from a closed workbook, no input validation!
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    If Not wb Is Nothing Then ' opened the workbook
        On Error Resume Next ' ignore errors
        With wb.Worksheets(strSourceWS).Range(strSourceRange)
        .Copy rngTarget
        .PasteSpecial xlPasteValues
        End With
        On Error GoTo 0 ' stop when errors occur
        wb.Close False ' close the source workbook without saving changes
        Set wb = Nothing ' free memory
        Worksheets("Avon Not Invoiced").Cells.Font.Size = 8
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Sub CopyNotInvoiced()

    Worksheets("Avon Not Invoiced").Range("A2:L250").ClearContents

    CopyFromNotInvoicedWB "E:\AccuRounds Daily Shipped Not Invoiced.xls", _
        "Sheet1", "A3:L250", Range("A2")
End Sub

Open in new window

0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 9
  • 8
1 Solution
 
Saurabh Singh TeotiaCommented:
Did you define the rng target properly.?
In other way you can do this to avoid the error, Replace these set of lines in the code...
With wb.Worksheets(strSourceWS).Range(strSourceRange)
        .Copy rngTarget
        .PasteSpecial xlPasteValues
        End With

with this..
wb.Worksheets(strSourceWS).Range(strSourceRange).value=wb.Worksheets(strSourceWS).Range(strSourceRange).value
This will do same as your code.
Saurabh...
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I tried your code and it's still not pasting anything. The error has gone away but nothing gets copied from my file. As I mentioned earlier this code has been working fine for a long time. I don't get why it would just stop working
0
 
Saurabh Singh TeotiaCommented:
Ahh,forget my earlier comment, I didn't read your code properly, foolishness on my part, You are getting this error, because your copied range that is "a3:l250" is a part of the paste range "a2" since you are not specifying where you want to paste it, so its pasting over in the same sheet from "a2".
Now this is what i assumed that you wanted to paste over activeworkbook from which you are running the code to sheet called -->Avon Not Invoiced.
Saurabh....

Sub CopyFromNotInvoicedWB(strSourceWB As String, _
    strSourceWS As String, strSourceRange As String, _
    rngTarget As Range)
' copies information from a closed workbook, no input validation!
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    If Not wb Is Nothing Then ' opened the workbook
        On Error Resume Next ' ignore errors
        With wb.Worksheets(strSourceWS).Range(strSourceRange)
        .Copy rngTarget
        .PasteSpecial xlPasteValues
        End With
        On Error GoTo 0 ' stop when errors occur
        wb.Close False ' close the source workbook without saving changes
        Set wb = Nothing ' free memory
        Worksheets("Avon Not Invoiced").Cells.Font.Size = 8
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub
 
Sub CopyNotInvoiced()
 Dim wb1 As Workbook
 Set wb1 = ActiveWorkbook
    Worksheets("Avon Not Invoiced").Range("A2:L250").ClearContents
 
    CopyFromNotInvoicedWB "E:\AccuRounds Daily Shipped Not Invoiced.xls", _
        "Sheet1", "A3:L250", wb1.Sheets("Avon Not Invoiced").Range("A2")
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I think it's something more than just an error in my code. There has to be something wrong with my excel. I tested both codes on another PC and it works fine. Any idea what might be wrong?
0
 
Saurabh Singh TeotiaCommented:
Is the new workbook which is opening in your pc, is at the same instance or is it a different instance as that can be a problem if excel is opening at a different instance.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
How do i tell?
0
 
Saurabh Singh TeotiaCommented:
Okay, to check the same, try to apply a simple formula linking both the workbooks and see that whether you are able to select the another workbook in the formula, or rather just try to copy manually and then go to the other workbook and then do paste special and see you get the option of values in it or not..?
 
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes I was able to link both workbooks in a formula. I was also able to use the paste special/values as well. Everything seems normal. I'm no longer getting the error message anymore but the code still isn't working. It's clearing the contents as my code states but it's not pasting the values.
0
 
Saurabh Singh TeotiaCommented:
well did you tried my modified version..?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
yes I tried both versions. Both didn't give me the error but neither one pasted the values. I tested both versions on another PC and both worked fine. That leads me to believe something is wrong with excel. I tried repairing office but that didn't solve the problem.
0
 
Saurabh Singh TeotiaCommented:
Weird, I wont be able to comment whats wrong, till the time i see both the files and again when the macro is running are you doing something else as well in your pc..?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'll upload the two files for you to test. You'll have to change the path in the code to the Sample Source File.
No, the only thing I'm running is the excel spreadsheet just like I always do.
Sample-File.xls
Sample-Source-File.xls
0
 
Saurabh Singh TeotiaCommented:
Use this one and see this works or not..
Saurabh...

Sub CopyFromNotInvoicedWB(strSourceWB As String, _
    strSourceWS As String, strSourceRange As String, _
    rngTarget As Range)
' copies information from a closed workbook, no input validation!
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    If Not wb Is Nothing Then ' opened the workbook
        On Error Resume Next ' ignore errors
        With wb.Worksheets(strSourceWS).Range(strSourceRange)
        .Copy
        rngTarget.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        End With
        On Error GoTo 0 ' stop when errors occur
        wb.Close False ' close the source workbook without saving changes
        Set wb = Nothing ' free memory
        Worksheets("Avon Not Invoiced").Cells.Font.Size = 8
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub
  
Sub CopyNotInvoiced()
 Dim wb1 As Workbook
 Set wb1 = ActiveWorkbook
    Worksheets("Avon Not Invoiced").Range("A2:L250").ClearContents
  
    CopyFromNotInvoicedWB "E:\AccuRounds Daily Shipped Not Invoiced.xls", _
        "Sheet1", "A3:L250", wb1.Sheets("Avon Not Invoiced").Range("A2")
End Sub

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok now that worked. What was wrong with my code?
I still can't figure out why it just stopped working like that. Any ideas?
0
 
Saurabh Singh TeotiaCommented:
What i can make out that your are doing a copy which is copying the formulas over since the paste special values is not properly been done and the raw-data from which you are copying are not values but formulas because of which its not working, i revised the code again to copy the values correctly.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I understand what you're saying but it still doesn't make sense that the code would just stop working like that. I guess at this point it really doesn't matter since it's working now.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much for your help. I greatly appreciate it! This was a tough one for me and I'm glad you got it to work for me!!!!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now