excel - 使用指定日期从一个工作簿复制到另一个工作簿

Question

我正在尝试输入日期，然后根据这两个日期过滤数据。我能够弄清楚如何选择所需的日期，但过滤时没有运气。另外，我有没有机会在过滤日期之前清除所有过滤器？这个工作簿被很多人使用，我想确保日期是唯一被过滤的东西。

错误是： Run-Time error '1004' 范围类的自动过滤方法失败 我的问题是 rngfull .Autofilter 行

这是我的代码：

  Public Sub CreateSubsetWorkbook(StartDate As String, EndDate As String)

        Dim wbkInput As Workbook, wbkOutput As Workbook
        Dim wksInput As Worksheet, wks As Worksheet, wksOutput As Worksheet
        Dim lngLastRow As Long, lngLastCol As Long, lngDateCol As Long
        Dim rngFull As Range, rngResult As Range, rngTarget As Range
        Dim Lastrow As Integer
        lngDateCol = 2
        Set wbkOutput = ThisWorkbook
        Set wbkInput = Workbooks.Open("f:\Sharta\red files\DBr\(NEW SERVER) with Macro.xlsm")
        Set wksInput = wbkInput.Sheets("Sheet1")

            With wksInput
                Set wksOutput = wbkOutput.Sheets("Data Dump")
                Set rngTarget = wksOutput.Cells(1, 1)

                lngLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlPrevious).Row
                lngLastCol = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                                     SearchOrder:=xlByColumns, _
                                     SearchDirection:=xlPrevious).Column
                Set rngFull = .Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol))

                With rngFull
                    Range.AutoFilter Field:=lngDateCol, _
                                Criteria1:=">=" & StartDate, _
                                Criteria2:="<=" & EndDate

                    Set rngResult = rngFull.SpecialCells(xlCellTypeVisible)
                    rngResult.Copy Destination:=rngTarget
                End With

                .AutoFilterMode = False
                If .FilterMode = True Then
                    .ShowAllData
                End If
            End With


        wbkInput.Close Savechanges:=False
        wksOutput.Activate

    End Sub

标签： excelvba