title: How can I reference an Excel worksheet without hardcoding its name in VBA?
body: <p>I am creating a Macro for excel for copying one excel sheet to another excel sheet.</p>
  
  <p><em>This is working:</em></p>
  
  <pre><code>Function CopyFile()
  
     Dim Wb1 As Workbook
     Dim Wb2 As Workbook
     Dim ws1 As Worksheet
     Dim ws2 As Worksheet
  
     Set Wb1 = ActiveWorkbook   
     Set ws1 = Wb1.Worksheets("Task")
     Set Wb2 = Workbooks.Open("D:\Outlook\dest.xlsx")
  
     ws1.Copy Wb2.Worksheets(1)
  
     Wb2.Save
     Wb2.Close
     Wb1.Activate   
  
  End Function
  </code></pre>
  
  <p>But i don't want to use a hard coded string to get the sheet name  <strong>Set ws1 = Wb1.Worksheets("Task")</strong>. Instead want to use the function argument.</p>
  
  <p>Following piece of code does not work:</p>
  
  <pre><code>Function CopyFile(name As String)
  
     Dim Wb1 As Workbook
     Dim Wb2 As Workbook
     Dim ws1 As Worksheet
     Dim ws2 As Worksheet
  
     Set Wb1 = ActiveWorkbook
  
     Set ws1 = Wb1.Worksheets(name)
     Set Wb2 = Workbooks.Open("D:\Outlook\dest.xlsx")
     ws1.Copy Wb2.Worksheets(1)
  
     Wb2.Save
     Wb2.Close
     Wb1.Activate
  
  End Function
  </code></pre>