title: Why won't ActiveSheet.Name function work in vba?
body: <p>I want to create a new (separate) workbooks for each worksheets from Test_Main but I am getting a</p>
  <blockquote>
  <p>1004 error</p>
  </blockquote>
  <p>in this line of code stating that I need a new name and cannot use this name as it is in use.</p>
  <pre><code>wb.SaveAs &quot;H:\IT\Melissa\Essengeld\TEST&quot; &amp; &quot;\&quot; &amp; ActiveSheet.Name &amp; &quot;.csv&quot;
  </code></pre>
  <p>Is my code right? Or is there any other alternative for this ?</p>
  <p>Thanks in advance !</p>
  <pre><code>
  Private Sub CommandButton3_Click()
  
  Dim a As Integer
  Dim ws As Worksheet
  Dim wb As Workbook
  
  a = ThisWorkbook.Worksheets.Count  'counts all the sheets
  
  For i = 1 To a 'loops for all sheets
  If ThisWorkbook.Worksheets(i).Name &lt;&gt; &quot;Test_Main&quot; Then 'rule out the main sheet
  
  Set wb = Workbooks.Add
  ThisWorkbook.Worksheets(i).Copy before:=wb.Worksheets(1) 'new workbook has 1 worksheet by deafult
  wb.SaveAs &quot;H:\IT\Melissa\Essengeld\TEST&quot; &amp; &quot;\&quot; &amp; ActiveSheet.Name &amp; &quot;.csv&quot;
  
  wb.Close savechages = True
  
  End If
  Next i
  
  ThisWorkbook.Activate
  ThisWorkbook.Sheets(1).Activate
  ThisWorkbook.Sheets(1).Cells(1, 1).Select
  
  MsgBox (&quot;Task Completed&quot;)
  End Sub
  
  </code></pre>