title: Excel VBA macro to create multiple workbooks that are macro free
body: <p>I asked this question a few weeks ago but I have not had a response -  I am really stuck. The reason I have to do this is to try resolve a mess a contractor left - I normally have very little contact with VBA so this is too complex for my basic knowledge. I want the macro to do the following:</p>
  
  <ol>
  <li>Loop through a column of selected rows on a sheet in my workbook to get the names for each of the new workbooks to be created (I have this working)</li>
  <li>Refresh all data within the newly created workbook (I have this working)</li>
  <li>copy/paste values on one of the sheets in newly created book (not yet but I guess this is straight-forward)</li>
  <li>delete two sheets in the workbook (I have this working)</li>
  <li>delete the macro in the new workbook (help from here on!!)</li>
  <li>save and close newly created workbook</li>
  <li>Move onto next workbook to be created </li>
  <li>Once all are finished, return to template or close template - either or.</li>
  </ol>
  
  <p>here is the code I already have:</p>
  
  <pre><code>Sub Button3_Click()
  
  Dim MyCell As Range, MyRange As Range
  Dim currentSheet As Excel.Worksheet
  Dim LR As Long
  Set currentSheet = ActiveSheet
  
  LR = Range("A" &amp; Rows.Count).End(xlUp).Row
  
  'this gets the values for workbook names
  Set MyRange = Range("A2:A" &amp; LR).SpecialCells(xlCellTypeVisible)
  For Each MyCell In MyRange
    'this populates a cell with the name in the range that the workbook then references for refreshing an MS query  
  Worksheets("Front Sheet").Cells(5, 5) = MyCell.Value
  
      ActiveWorkbook.RefreshAll
      ActiveWorkbook.SaveAs Filename:="P:\Informatics\S&amp;L scorecards\Clinical Scorecard Template\test\" &amp; MyCell.Value &amp; ".xls"
  
  ' code here to delete worksheets and delete macro?
  
      Next MyCell 
  End Sub
  </code></pre>
  
  <p>Thanks in advance</p>