如何修复编译错误：预期End Sub？

I am trying to create a macro that when a line of information is added to one of my sheets, it will automatically add that line of data to a different sheet (in the same workbook). I have researched extensively online and found this code which I think does what I want it to, after I tweaked it a little:

我正在尝试创建一个宏,当一行信息添加到我的一个工作表时,它会自动将该行数据添加到另一个工作表(在同一工作簿中)。我已经在网上进行了广泛的研究,发现这个代码我觉得我做了我想要的,在我调整了一下之后:

Sub addrow()

Public Sub worksheet_change(ByVal target As Range)

    Set sourcebook = ThisWorkbook
    Set sourcesheet = sourcebook.Worksheets("sheet1")

    Set targetbook = ThisWorkbook
    Set targetsheet = targetbook.Worksheets("sheet10")

    If sourcesheet.Cells(198, 16).Value = "Auto" Or _
        sourcesheet.Cells(198, 16).Value = "Connect" Or _
        sourcesheet.Cells(198, 16).Value = "Multiple*" Or _
        sourcesheet.Cells(198, 16).Value = "Property" Or _
        sourcesheet.Cells(198, 16).Value = "Umbrella" Or _
        sourcesheet.Cells(198, 16).Value = "WC" Then
        GoTo link
    Else
        GoTo insertion
    End If

    insertion: targetsheet.Activate
    ActiveSheet.Rows(198).EntireRow.Insert

    sourcesheet.Activate

link:
    'targetsheet.Cells(194, targetsheet.Range("initial response").Column) = sourcesheet.Cells(198, 16).Value
    targetsheet.Cells(194, 16) = sourcesheet.Cells(198, 16).Value

    targetsheet.Cells(194, 16) = sourcesheet.Cells(198, 16).Value

End Sub

When I try to run the code, I get the error message "Compile Error: Expected End Sub" and it highlights that first line of code- Sub addrow(). When I try taking this line out, VBA requires me to create a new macro when I try and run it, which then adds that line back in and I am back to square one.

当我尝试运行代码时,我收到错误消息“Compile Error:Expected End Sub”,它突出显示第一行代码 - Sub addrow()。当我尝试使用这一行时,VBA要求我在尝试运行它时创建一个新宏,然后将该行重新添加回来并回到原点。

1 个解决方案

#1

Focus on first 3 lines. You have two Sub declarations there. Keep only one. Perhaps remove line

专注于前3行。那里有两个Sub声明。只保留一个。也许删除线

Public Sub worksheet_change(ByVal target As Range)

I think you should remove this line and not the other one because it seems to be forgotten from some previous work. Its parameter target is not used in the code and what your code does better fits with name addrow than with worksheet_change.

我认为你应该删除这一行,而不是另一行,因为它似乎从以前的一些工作中被遗忘了。它的参数目标没有在代码中使用,你的代码更适合使用名称addrow而不是workheet_change。

This is your code refactored:

这是您重构的代码:

variable names keep VBA naming convention

变量名称保持VBA命名约定

blocks of code rearranged so gotos and labels could be excluded

重新排列的代码块,因此可以排除gotos和标签

Sub AddRow()

    Set SourceBook = ThisWorkbook
    Set SourceSheet = SourceBook.Worksheets("sheet1")

    Set TargetBook = ThisWorkbook
    Set TargetSheet = TargetBook.Worksheets("sheet10")

    If Not (SourceSheet.Cells(198, 16).Value = "Auto"
            Or SourceSheet.Cells(198, 16).Value = "Connect"
            Or SourceSheet.Cells(198, 16).Value = "Multiple*"
            Or SourceSheet.Cells(198, 16).Value = "Property"
            Or SourceSheet.Cells(198, 16).Value = "Umbrella"
            Or SourceSheet.Cells(198, 16).Value = "WC") Then
        TargetSheet.Activate
        ActiveSheet.Rows(198).EntireRow.Insert
        SourceSheet.Activate
    End If

    'TargetSheet.Cells(194, TargetSheet.Range("initial response").Column) = SourceSheet.Cells(198, 16).Value
    TargetSheet.Cells(194, 16) = SourceSheet.Cells(198, 16).Value
    TargetSheet.Cells(194, 16) = SourceSheet.Cells(198, 16).Value

End Sub

#1