title: Paste special values and transpose into a new workbook
body: <p>I have a workbook with multiple rows.  I am trying to copying a selection one row at a time and paste (values and transposed) into a template spreadsheet.  I can open the blank template spreadsheet and select the data that I want to paste but am having a problem getting the data to paste values and transpose.</p>
  <p>I am also having a problem saving the template somewhere new with a meaningful name.</p>
  <p>Here is my code:</p>
  <pre><code> Function CreateIndividualFiles(ash, individualTempPath, individualCompletedPath)
  
  Do While ash.Cells(8 + n, 2) &lt;&gt; &quot;&quot;
  
      Dim xl0 As New Excel.Application
      Dim xlw As New Excel.Workbook
      
      Set xlw = xl0.Workbooks.Open(individualTempPath)
      
      ash.Range(Cells(8 + n, 2), Cells(8 + n, 49)).Select
      Selection.Copy
      
      xl0.Worksheets.Add
      
      xl0.ActiveSheet.Cells(6, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=True
      
      xl0.Workbook.SaveAs sFilePath + &quot;\Split\&quot; &amp; DeptDirName &amp; &quot;\&quot; &amp; departmentName &amp; &quot;\&quot; &amp; DeptFileName, fileFormat:=51
      
      
      xlw.Close
      
      Set xl0 = Nothing
      Set xlw = Nothing
  
  
  n = n + 1
      
  Loop
      
  
  End Function
  </code></pre>