title: Creating a new excel worksheet via .Copy() and moving it to the last position
body: <p>I've been at this for quite a while,</p>
  
  <p>I want to create a new worksheet by copying another. This places the new sheet before the sheet being copied. I then want to move it to the very end of all of the sheets.</p>
  
  <p>The aim is to have worksheets called > summary1 > summary2 > summary3 .. etc in that order</p>
  
  <p>Here is what I have (excuse brevity)</p>
  
  <pre><code>$ex = New-Object -ComObject Excel.Application
  $ex.Visible = $true
  $wb = $ex.Workbooks.Add()
  
  for ($i = 1; $i -le 3; $i++)
  {
      $wb.Worksheets.Item(2).Copy($wb.Worksheets.Item(2))
      $newSheet = $wb.Worksheets.Item(2)
      $newSheet.Activate()
      $name = "Summary$i"
      $newSheet.Name = $name
      $wb.Worksheets.Item($name).Move($wb.Worksheets.Item($i + 1))        
  }
  </code></pre>
  
  <p>It works for the first sheet, it renames the sheet then moves it to the end but after that everytime it reaches the .Move method it doesn't do anything except .Activate() the original "Summary" sheet.</p>
  
  <p>I have no idea how to explain this behaviour.. Thankyou kindly in advance.</p>
  
  <p>Edit: Changed $wb.Worksheets.Item("Summary").Move to $wb.Worksheets.Item($name).Move</p>
  
  <p>Edit:</p>
  
  <p>Here is the solution:</p>
  
  <pre><code>for ($i = 1; $i -le 3; $i++)
  {
      $wb.Worksheets.Item(2).Copy($wb.Worksheets.Item(2))
      $newSheet = $wb.Worksheets.Item(2)
      $newSheet.Activate()
      $name = "Summary$i"
      $newSheet.Name = $name
  
      $lastSheet = $wb.WorkSheets.Item($wb.WorkSheets.Count) 
      $newSheet.Move([System.Reflection.Missing]::Value, $lastSheet)      
  }
  </code></pre>