title: Copying range and pasting into new workbook
body: <p>This should be really simple, but I've been trawling forums and SO answers for hours to find the answer with no luck, so am (reluctantly) creating a question of my own.</p>
  
  <p>What I'm trying to do is simply create a new workbook, and paste a range from another workbook into that workbook. Sounds simple..?</p>
  
  <p>My original workbook, let's call Book1. I'm trying to create a new workbook, Book2, which I will copy the <em>values</em> of cells A1:B10 to.</p>
  
  <p>Here's one version of my code (starting with Book1 open):</p>
  
  <pre><code>Range("A1:B10").Copy
  Set NewBook = Workbooks.Add
      With NewBook
          .SaveAs Filename:="Book2.xls"
      End With
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  </code></pre>
  
  <p>This gives a "PasteSpecial of Range class failed" error. I have tried the following fixes with no luck:</p>
  
  <ul>
  <li>added 'Workbooks("Book2.xls").Activate' to the code</li>
  <li>removed the extra arguments in the PasteSpecial line</li>
  <li>tried '.Paste' instead of '.PasteSpecial'</li>
  <li>changed 'Selection.PasteSpecial' to 'ActiveSheet.PasteSpecial'</li>
  <li>explicitly referencing the copy range, including the workbook and sheet reference</li>
  <li>creating the new workbook first, then performing the copy, before reactivating the new workbook and pasting</li>
  </ul>
  
  <p>None of the above solutions work... any wisdom at this stage would be gratefully received!</p>