Filter, copy and paste special in new Excel worksheet

Question

I want to use VBS in all worksheets of an Excel document which will do the following actions:

1. Filter the table
2. Copy filtered content
3. Create new worksheet
4. Paste Special values only and save as csv file

I try to achieve this with this code but it does not work

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

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.

Answer 1

Worksheet objects don't have an ActiveWorksheet property, but the Application object has a property ActiveSheet. Same goes for the ActiveWorkbook property. I would also strongly recommend using & instead of + for string concatenations.

Change this:

.ActiveWorksheet.Range("A1").PasteSpecial -4122
.ActiveWorkbook.SaveAs home_directory+x.Name + ".csv", 23

into either this:

excelObject1.ActiveSheet.Range("A1").PasteSpecial -4122
excelObject1.ActiveWorkbook.SaveAs home_directory & x.Name & ".csv", 23

or this:

.Application.ActiveSheet.Range("A1").PasteSpecial -4122
.Application.ActiveWorkbook.SaveAs home_directory & x.Name & ".csv", 23