title: Excel Macro to create a report file
body: <p>I have an Excel workbook with two worksheets, Report and Data.  I want to write a section of the Report tab out to a new workbook file (print range named "Roster") and retain the formatting, print settings, etc.</p>
  
  <p>Below is the macro I have so far - it works but writes the whole Report tab to the file, not just the roster section, and it loses the print range which would be useful for the recipient of the resulting file.</p>
  
  <pre><code>Sub Macro1()
  '
  ' Macro1 Macro
  '
  ' Keyboard Shortcut: Ctrl+t
  '
    Dim Output As Workbook
    Dim FileName As String
  
  
    'This part updates the roster - grabs the next
    'roster value and move it to A1, thus updating the report
  
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.End(xlDown).Select
    Selection.ClearContents
    Range("A1").Select
  
   'Now we write the Report worksheet to a new file using
   'the custom filename in cell AA1
  
    Set Output = Workbooks.Add
    Application.DisplayAlerts = False
  
    ThisWorkbook.Worksheets("Report").Cells.Copy
  
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=True, Transpose:=False
  
    Selection.PasteSpecial Paste:=xlPasteFormats
  
    FileName = Range("AA1").Value
  
    Output.SaveAs FileName
    Output.Close
  
  End Sub
  </code></pre>