title: VBA Excel Paste works, PasteSpecial does not
body: <p>I am trying to export ranges (as csv) from a multisheet spreadsheet by copying and pasting to another sheet. This works perfectly for the Paste function, but not for the PasteSpecial function. </p>
  
  <p>This is fine:</p>
  
  <pre><code>Sheets("Company").Select
    Range("Company_Data").Select
      Selection.Copy
      Workbooks.Add
      ActiveSheet.Paste
      ActiveWorkbook.SaveAs Filename:= _
      "D:\Documents and Settings\Charles\Development\G4H Dev\Book3.csv" _
      , FileFormat:=xlCSV, CreateBackup:=False
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.DisplayAlerts = True
  </code></pre>
  
  <p>But this returns Error 1004</p>
  
  <pre><code>`Sheets("Output").Select
    Range("Output_Data").Select
      Selection.Copy
      Workbooks.Add
      ActiveSheet.PasteSpecial xlPasteValues
      ActiveWorkbook.SaveAs Filename:= _
      "D:\Documents and Settings\Charles\Development\G4H Dev\Book2.csv" _
      , FileFormat:=xlCSV, CreateBackup:=False
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.DisplayAlerts = True
  </code></pre>
  
  <p>I have tried a variety of options including </p>
  
  <pre><code>format:=xlPasteValues
  </code></pre>
  
  <p>but nothing I can find either in the MS documentation of my own reference books appears to give anything but the most cursory (and unhelpful) treament.</p>