title: VBA paste sheets from array to new workbook as values
body: <p>I'm trying to write this little macro to copy several sheets stored in an array and paste them as values to new workbook, maintaining sheets' names and order. I've found some solutions but not exactly matching my situation.</p>
  
  <pre><code>Sub tryit()
  
  Dim wo As Workbook, wn As Workbook
  Dim so As Worksheet, sn As Worksheet
  Dim MyArray As Variant
  
  MyArray = Array("ar1", "ar2", "ar3")
  
  Set wo = ActiveWorkbook
  Set wn = Workbooks.Add
  
  For Each so In wo.Worksheets(MyArray)
          Set sn = wn.Worksheets.Add
          sn.Name = so.Name
          so.Cells.Copy
          sn.[A1].PasteSpecial xlPasteValues
          sn.[A1].PasteSpecial xlPasteFormats
      Next so
  
  wn.SaveAs Filename:=ThisWorkbook.Path &amp; "\" &amp; "Report"
  wn.Close savechanges:=True
  
  End Sub
  </code></pre>
  
  <p>Problems with result:</p>
  
  <ol>
  <li>I'm adding new worksheets from array to sheets 1 2 and 3, how to replace them instead of deleting?</li>
  <li>Order of added worksheets is reversed (ark3, ark2, ark1).</li>
  </ol>
  
  <p>I've seen some solutions with use of Lbound and Ubound functions, but I'm not sure how to combine them with loop, and I guess it would make code cleaner?</p>
  
  <p>Thanks in advance.</p>