vba - 将筛选的工作表导出为 CSV 文件

Question

我目前正在使用此代码导出整个工作表，但我只想导出满足条件的行。在这种情况下，第 13 列中有一个“x”:

Option Explicit
Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
End With        

Dim Change below to "- 4"  to become compatible with .xls files
MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

我尝试使用自动过滤器调整代码，但没有成功。
我换了线

ActiveWorkbook.ActiveSheet.UsedRange.Copy

有了这个:

ActiveSheet.Range("$A$1:$M$81").AutoFilter Field:=13, Criteria1:="x"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

但是我收到 Range Class 的 Autofiltermethod failed 错误。

Answer 1

有时更容易摆脱你不想要的东西，而不是复制你想要的东西。

Sub ExportAsCSV()
    Dim myFileName As String

    With ActiveWorkbook
        myFileName = Left(.FullName, InStrRev(.Name, Chr(46)) - 1) 'note no extension needed
        .ActiveSheet.Copy   'makes a copy in a new active workbook
    End With

    'new ActiveWorkbook with copy of worksheet
    With ActiveWorkbook
        With .Worksheets(1)
            If .AutoFilterMode Then .AutoFilterMode = False
            With .Cells(1, 1).CurrentRegion
                .Value = .Value
                .AutoFilter field:=13, criteria1:="<>x"
                With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    End If
                End With
            End With
            If .AutoFilterMode Then .AutoFilterMode = False
        End With
        .SaveAs Filename:=myFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
        .Close SaveChanges:=False
    End With

    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
End Sub