title: Copy a sheet from one workbook and save in a new workbook with the name being a value in a cell
body: <p>I would like to copy Sheet2 from Workbook1 and paste it in Sheet1 of a new Workbook and save the new Workbook as the value of cell A1 in Sheet2, Workbook 1. I would like this macro to fire when I click a button that I have added.</p>
  
  <p>Here is what I have done so far but it creates two Workbooks
  - One with the correct name but not the content of Sheet2, Workbook1
  - And another that has the content of Sheet2, Workbook1 but not the correct Workbook name</p>
  
  <pre><code>Sub CreateWorkbook()
  
  Dim n As String
  n = ThisWorkbook.Path &amp; "\" &amp; Range("A1").Value &amp; ".xls"
  Workbooks.Add
  ActiveWorkbook.SaveAs n
  ThisWorkbook.Sheets("Sheet2").Copy
  ActiveWorkbook.Sheets("Sheet1").PasteSpecial xlPasteValues
  
  End Sub
  </code></pre>
  
  <p>What am I missing here?</p>