title: Copy a worksheet to a new workbook with paste values and keeping formats
body: <p>I am trying to copy one worksheet to a new workbook, pasting all formulas as values while remaining all formats, sheetname, etcetera. The new file name should be "University" and stored on the same location as the original file.
  I have been struggling with this, as it keeps returning an </p>
  
  <blockquote>
    <p>"Error 1004: PasteSpecial method of Range class failed" </p>
  </blockquote>
  
  <p>with my current (copied) script:</p>
  
  <pre><code>Sub new_workbook()
  
  Dim Output As Workbook
  Dim FileName As String
  
  Set Output = Workbooks.Add
  Application.DisplayAlerts = False
  
  ThisWorkbook.Worksheets("Report").Copy
  
  Selection.PasteSpecial Paste:=xlPasteValues, _
  Operation:=xlNone, SkipBlanks:=True, Transpose:=False
  
  FileName = ThisWorkbook.Path &amp; "\" &amp; "University.xlsx"
  Output.SaveAs FileName
  
  End Sub
  </code></pre>