title: Filter, copy and paste special in new Excel worksheet
body: <p>I want to use VBS in all worksheets of an Excel document which will do the following actions:</p>
  
  <ol>
  <li>Filter the table   </li>
  <li>Copy filtered content</li>
  <li>Create new worksheet</li>
  <li>Paste Special values only and save as csv file</li>
  </ol>
  
  <p>I try to achieve this with this code but it does not work</p>
  
  <pre><code>With excelObject1
      .Application.DisplayAlerts = False
  
      for each x in excelObject1.WorkSheets
  
          x.Rows(1).AutoFilter 1, "type 1"
          x.Range("A1:E41").Copy
          .WorkSheets.Add
          .ActiveWorksheet.Range("A1").PasteSpecial -4122
          .ActiveWorkbook.SaveAs home_directory+x.Name + ".csv", 23
  
      Next 
  
      .Quit
      .Application.DisplayAlerts = True
  End With
  </code></pre>
  
  <p>It gives error (Object doesn't support this property or method: 'ActiveWorksheet') and if I remove the dot in front of ActiveWorksheet then it gives error (Variable undefined 'ActiveWorksheet')
  I don't know how to create new worksheet within the "for each x".
  Maybe this is not the way I shold do it?
  I try to do this with VBS, but it will be great if someone can helo me to do it even with VBA. My Excel is 2013.</p>