title: Can you copy a worksheet and keep a reference to the new sheet?
body: <p>Is it possible to copy a sheet using VBA and keep a reference to it?  I know I can do the following to keep a reference to the new worksheet, but I have to keep track of the indices and it seems like there should already be something built in that can do it.</p>
  
  <pre><code>Dim newws as Worksheet
  Dim oldws as Worksheet
  
  ' set oldws to whatever I need, then
  oldws.Copy Before:=Worksheets(1)
  Set newws = Worksheets(1)
  </code></pre>
  
  <p>But it would just be cleaner if something like this were available, like it is for the Worksheets.Add</p>
  
  <pre><code>' You can do this
  Set newws = Worksheets.Add    
  
  ' But definitely not this, because Sheets.Copy doesn't return anything.
  Set newws = oldws.Copy Before:=1
  </code></pre>
  
  <p>I suppose I could write my own function:</p>
  
  <pre><code>Function CopyAWorksheet(ws As Worksheet, Before As Worksheet) As Worksheet
      ws.Copy Before:=Before
      Set CopyAWorksheet = Worksheets(Before.Index - 1)
  End Function
  </code></pre>
  
  <p>I could add an <code>After</code> argument and make both <code>Before</code> and <code>After</code> variants so that it would act exactly like I want, but it seems likely that I'm just missing something that's already built in to Excel VBA.  If there are interesting technical reasons that this is not possible, I would love to hear those too.</p>
  
  <p>I'm using Excel 2010.</p>