excel - 编写 VBA 时，我无法运行两个特定错误

Question

我的第一行代码使用无效，并且在输入我的对象作为范围时，对象“_Worksheet”的方法“范围”失败。有人可以帮忙吗？

Sub SplitandFilterSheet()

    Dim SplitOrderNum As Range

    Sheets("Sum To Line Item").Select

    Set SplitOrderNum = Range("SplitOrderNum")

    For Each cell In SplitOrderNum
        Sheets("Sum To Line Item").Copy After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = cell.Value

        With ActiveWorkbook.Sheets(cell.Value).Range("OrderData")
            .AutoFilter Field:=2, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        ActiveSheet.AutoFilter.ShowAllData
    Next cell
End Sub

标签： excelvba

Answer 1

试试这段代码，阅读里面的注释并寻找 <<<< Customize this >>> 行：

Sub SplitandFilterSheet()

    ' Declare objects
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim splitOrderNum As Range
    Dim sourceCell As Range

    ' Declare other variables
    Dim sourceSheetName As String
    Dim sourceRangeName As String
    Dim targetSheetName As String
    Dim targetRangeName As String

    Dim lastSheetHidden As Boolean


    ' <<< Customize this >>>
    sourceSheetName = "Sum To Line Item"
    sourceRangeName = "SplitOrderNum"
    targetRangeName = "OrderData"

    ' Initialize the source sheet
    Set sourceSheet = ThisWorkbook.Sheets(sourceSheetName)

    ' Initialize the range (Add full qualifier to the current workbook, sheet and range)
    Set splitOrderNum = sourceSheet.Range(sourceRangeName)

    ' Get if last sheet is visible in current workbook
    lastSheetHidden = Not ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Visible
    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Visible = True

    For Each sourceCell In splitOrderNum
        ' Copy the source worksheet
        sourceSheet.Copy After:=Worksheets(ThisWorkbook.Sheets.Count)
        ' Rename the new worksheet
        Sheets(ThisWorkbook.Sheets.Count).Name = sourceCell.Value
        ' Reference to the added worksheet
        Set targetSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

        With targetSheet.Range(targetRangeName)
            .AutoFilter Field:=2, Criteria1:="<>" & sourceCell.Value, Operator:=xlFilterValues
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        ' Check this next line if this should point to the orderdata range too (?)
        targetSheet.AutoFilter.ShowAllData

    Next sourceCell

    ' Return the last sheet visible state
    If lastSheetHidden = False Then
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Visible = Not lastSheetHidden
    End If

End Sub