Excel VBA - Copy Sheet to new workbook X times
<p>I need to copy the same worksheet X times (x = sheet2 row A) into a new workbook.</p>

<p>For each copy I need to:</p>

<p>1.Change a drop down to display the next value </p>

<p>2.Do a Refresh (Workbook is connected to a database which pulls different information based on the value of the drop down and is not automatically refreshed)</p>

<p>3.Copy just the values (no formulas)</p>

<ol start="4">
<li><p>Rename the sheet to the value of the drop down.</p></li>
<li><p>Save all of the copied worksheets into  1 workbook</p></li>
</ol>

<p>My code (below) which is called on a button press currently saves the sheet X times based on sheet2 rowA (as intended).</p>

<p>It is missing steps 1,2,4 and 5</p>

<p>The code I have at the moment (called on button click)</p>

<pre><code>Dim x As Integer    '~~&gt;Loop counter
Dim WS As Worksheet
Dim LastCellA As Range, LastCellB As Range
Dim LastCellRowNumber As Long

Set WS = Worksheets("Sheet2")    '~~&gt;Sheet with names
With WS
    Set LastCellA = .Cells(.Rows.Count, "A").End(xlUp)    '~~&gt;Column with names.
    '~~&gt;This needs to be changed to find the range as data may not start at A1

    x = Application.WorksheetFunction.Max(LastCellA.Row)
End With



For numtimes = 1 To x
    ActiveWorkbook.Sheets("Sheet1").Copy _
            After:=ActiveWorkbook.Sheets(Worksheets.Count)
    '~~&gt;Copy values only
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Next
</code></pre>