copy and paste error causing workbook to crash

Question

I'm having an issue with copy and pasting from one spreadsheet to another.

I am using the following code:

Sub LoadnH()
Dim NF As Workbook
Dim shtMain As Worksheet

Set shtMain = Worksheets("Main")
Dim filePath As String
Dim strFileName As Variant
    strFileName = Application.GetOpenFilename("All Files (*.*), *.*", , "Select File to Import", , False)
    shtMain.Range("filePath").Value = strFileName
    If strFileName <> False Then
        Set NF = Application.Workbooks.Open(strFileName)
        Application.CutCopyMode = False
        NF.Sheets("Summary").Copy
        Application.DisplayAlerts = False
        NF.Close False

Dim nH As Worksheet
Set nH = Worksheets("Hedge Data")
 nH.Activate
     With nH
        .Cells.Clear
        .Pictures.Delete
        .Range("A1").Select
        .PasteSpecial xlPasteValues
     End With
End If
End Sub

The code errors out at the following point

.PasteSpecial xlPasteValues

The code show a runtime error '1004': Method 'PasteSpecial' of object'_Worksheet' failed

how can I fix this so this error? Many times when it hits this error excel will crash and shutdown as well.

Answer 1

To Avoid Select and other similar methods you can assign your value of the destination range with the value from your source range.

1. You are using the Worksheet.Copy method which copies an entire Worksheet not the data in a Range of the worksheet. This will be creating a new copy of your source worksheet each time you run the code but not copying the data of the worksheet to the clipboard. (NB: below demonstrates using the Before parameter which dictates where the Worksheet will be copied to).
2. The Range.Copy method will copy the defined range's data to the clipboard (unless you specify the destination parameter).
3. Rather than using Copy/Paste etc. you can assign the value of the destination range with the value from your source range.

These examples below are all for demonstration of the above points and are tested using 2 new workbooks with default names for the workbooks and worksheets.

---

E.g 1

Sub WorksheetCopyMethod()
    Dim SourceWorksheet As Worksheet
    Dim DestinationwWorksheet As Worksheet
    
    Set SourceWorksheet = Workbooks("Book1").Sheets("Sheet1")
    Set DestinationWorksheet = Workbooks("Book2").Sheets("Sheet1")
    
    SourceWorksheet.Copy DestinationWorksheet
End Sub

The result of this test creates a copy of Sheet1 from Book1 before Sheet1 on Book2.

---

E.g 2

Sub RangeCopyMethod()
    Dim SourceWorksheet As Worksheet
    Dim DestinationwWorksheet As Worksheet
    
    Set SourceWorksheet = Workbooks("Book1").Sheets("Sheet1")
    Set DestinationWorksheet = Workbooks("Book2").Sheets("Sheet1")
    
    SourceWorksheet.Range("A1").Copy
    DestinationWorksheet.Range("A1").PasteSpecial xlPasteValues
End Sub

This example copies cell A1 from Book1 - Sheet1 and pastes it to cell A1 in Book2 - Sheet1.

---

E.g 3

Sub AvoidSelectMethod()
    Dim SourceWorksheet As Worksheet
    Dim DestinationwWorksheet As Worksheet
    
    Set SourceWorksheet = Workbooks("Book1").Sheets("Sheet1")
    Set DestinationWorksheet = Workbooks("Book2").Sheets("Sheet1")
    
    DestinationWorksheet.Range("A1").Value = SourceWorksheet.Range("A1").Value
End Sub

This example assigns the Value property of A1 from Book1 - Sheet1 to cell A1 in Book2 - Sheet1. It's the same outcome as E.g 2 but avoids using Select, Copy & Paste etc. This method is much faster and generally less error prone than the 2nd example.

Depending on your environment, the first example may be the easiest and quickest method.