Excel- VBA PasteSpecial Method of Range Class Failed

Question

Excel macro is intended to copy a range from one workbook to another using FileToOpen. The same code worked earlier today in separate workbook.

Error generated is Runtime 1004' PasteSpecial Method of class failed. Here's the section that fails:

    SrcWB.Worksheets("1").Range("A1:K35").Copy
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteValues)
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteFormats)

Error gets caught on both PasteSpecial Values and Formats. I also tried to using:

   SrcWB.Worksheets("1").Range("A1:K35").Value = TgtWB.Sheets("1").Range("A1:K35")

Above method didn't create any errors, however no values were transferred to the target workbook.

I've chewed on this most of this afternoon and would appreciate any help!

Here's the full code:

Sub CopySch()

Dim sh As Worksheet
Dim TgtWB As Workbook
Dim SrcWB As Workbook

Application.EnableEvents = False
Application.ScreenUpdating = False

Set TgtWB = ThisWorkbook

FileToOpen = Application.GetOpenFilename(FILEFILTER:="Excel Workbooks (*.xls*),*.xls*", Title:="Please select a file")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub

Else
    Set SrcWB = Workbooks.Open(FileToOpen, xlUpdateLinksNever, ReadOnly:=True)


    SrcWB.Worksheets("1").Range("A1:K35").Copy
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteValues)
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteFormats)

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

SrcWB.Close

End Sub



Worksheets("1") means a worksheet called "1" rather than the first worksheet in the book. Are the relevant worksheets in the source and target workbooks actually called "1"? If not, try Worksheets(1) — barrowc
"... however no values were transferred to the target workbook. ...". That's because you are copying from TgtWB to SrcWB — chris neilsen
@chris neilsen I tried TrgWB.Worksheets("1").Range("A1:K35").Value = ScrWB.Sheets("1").Range("A1:K35").Value and that worked perfectly — Jenga

noas noas · Accepted Answer · 2015-07-01T08:35:41

Goto the top of your module and add "Option Explicit".

Option Explicit

Sub CopySch()

Dim sh As Worksheet
Dim TgtWB As Workbook
Dim SrcWB As Workbook
...

It should solve your problem.

EDIT: lets give another try with this code. i have tried the code it Works without error.

Option Explicit
Sub CopySch()

Dim sh As Worksheet
Dim TgtWB As Workbook
Dim SrcWB As Workbook
Dim FileToOpen As String
Application.EnableEvents = False
Application.ScreenUpdating = False

Set TgtWB = ThisWorkbook

FileToOpen = Application.GetOpenFilename(FILEFILTER:="Excel Workbooks (*.xls*),*.xls*", Title:="Please select a file")

If FileToOpen = "False" Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub

Else
    Set SrcWB = Workbooks.Open(FileToOpen, xlUpdateLinksNever, ReadOnly:=True)

    SrcWB.Worksheets("1").Range("A1:K35").Copy
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteValues)
    TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteFormats)

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

SrcWB.Close

End Sub

Excel- VBA PasteSpecial Method of Range Class Failed

1 Answers