本质上我在Excel中有一个表格，我想先过滤，然后再把结果复制到一个新工作表。我尝试用.SpecialCells(xlCellsTypeVisible)来实现，但遇到了一个错误：

1004无法获取Range对象的SpecialCells属性。

调试时，我看到代码在那一步之前都能正常工作。它对某一列进行筛选，筛选出某个特定值。接着它应该选中所有可见单元格以便复制。下面是代码：

Public Function RangeToHTML_Export(strFeedRange As String) As String
On Error GoTo err_check

    Dim rng As Range
    Dim TempFile As String
    Dim TempWB As Workbook
    Dim fso As Object
    Dim ts As Object
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim lastRow As Long
    Dim approverCol As Long
    Dim tempSheet As Worksheet
    Dim exportLastRow As Long
    Dim exportLastCol As Long

    ' Reference the named range
    ' Set rng = ThisWorkbook.Names("Export").RefersToRange
    ' Get range(A1:C7) for named ranged "Export"
    Set ws = ThisWorkbook.Sheets("General Updates")
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    approverCol = Application.Match("Name of Approver", ws.Rows(2), 0)
    ws.Range(ws.Cells(3, 1), ws.Cells(lastRow, lastCol)).AutoFilter _
        Field:=approverCol, _
        Criteria1:="John Smith"

    Set tempSheet = ThisWorkbook.Sheets.Add
    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) _
        .SpecialCells(xlCellsTypeVisible).Cells.Copy ' ERROR HERE

    tempSheet.Range("A1").PasteSpecial (xlPasteValues)
    tempSheet.Range("A1").PasteSpecial (xlPasteFormats)
    tempSheet.Range("A1").PasteSpecial (xlPasteColumnWidths)

    Application.CutCopyMode = False

    exportLastRow = tempSheet.Cells(tempSheet.Rows.Count, 1).End(xlUp).Row
    exportLastCol = tempSheet.Cells(2, tempSheet.Columns.Count).End(xlToLeft).Column

    Set rng = tempSheet.Range( _
        tempSheet.Cells(1, 1), _
        tempSheet.Cells(exportLastRow, exportLastCol))

解决方案

如下所示：

ws.Range(ws.Cells(3, 1), ws.Cells(lastRow, lastCol)).AutoFilter _
        Field:=approverCol, _
        Criteria1:="John Smith"

...看起来你的数据表头在第2行，但你用来检查最后一列的行却是第1行：

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column