title: Excel- VBA PasteSpecial Method of Range Class Failed
body: <p>Excel macro is intended to copy a range from one workbook to another using FileToOpen.  The same code worked earlier today in separate workbook. </p>
  
  <p>Error generated is Runtime 1004' PasteSpecial Method of class failed. Here's the section that fails: </p>
  
  <pre><code>    SrcWB.Worksheets("1").Range("A1:K35").Copy
      TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteValues)
      TgtWB.Sheets("1").Range("A1:K35").PasteSpecial (xlPasteFormats)
  </code></pre>
  
  <p>Error gets caught on both PasteSpecial Values and Formats.  I also tried to using: </p>
  
  <pre><code>   SrcWB.Worksheets("1").Range("A1:K35").Value = TgtWB.Sheets("1").Range("A1:K35")
  </code></pre>
  
  <p>Above method didn't create any errors, however no values were transferred to the target workbook.  </p>
  
  <p>I've chewed on this most of this afternoon and would appreciate any help! </p>
  
  <p>Here's the full code:</p>
  
  <pre><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
  </code></pre>
  
  <p></p>