excel - VB新手。我的代码生成错误 : "expecting End With". with-end with 语句如何工作？

Question

Sub RowRangeMove()
    Sheets.Add().Name = "CopySheet"
    With Sheets("BigDataSet - Copy")
        .Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
    With Sheets("BigDataSet - Copy")
        .Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
    With Sheets("BigDataSet - Copy")
        .Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
    With Sheets("BigDataSet - Copy")
        .Range("D65000", .Range("D13000").End(xlUp)).Copy Destination:=Range("D1")
    With Sheets("BigDataSet - Copy")
        .Range("E65000", .Range("E13000").End(xlUp)).Copy Destination:=Range("E1")
    With Sheets("BigDataSet - Copy")
        .Range("F65000", .Range("F13000").End(xlUp)).Copy Destination:=Range("F1")
    With Sheets("BigDataSet - Copy")
        .Range("G65000", .Range("G13000").End(xlUp)).Copy Destination:=Range("G1")
    With Sheets("BigDataSet - Copy")
        .Range("H65000", .Range("H13000").End(xlUp)).Copy Destination:=Range("H1")
    With Sheets("BigDataSet - Copy")
        .Range("I65000", .Range("I13000").End(xlUp)).Copy Destination:=Range("I1")
    With Sheets("BigDataSet - Copy")
        .Range("J65000", .Range("J13000").End(xlUp)).Copy Destination:=Range("J1")
    End With
    .
    .
    [iterate several times]
    .
    .
    Sheets.Add().Name = "CopySheet7"
    With Sheets("BigDataSet - Copy")
        .Range("A455006", .Range("A502750").End(xlUp)).Copy Destination:=Range("A1")
    With Sheets("BigDataSet - Copy")
        .Range("B455006", .Range("B502750").End(xlUp)).Copy Destination:=Range("B1")
    With Sheets("BigDataSet - Copy")
        .Range("C455006", .Range("C502750").End(xlUp)).Copy Destination:=Range("C1")
    With Sheets("BigDataSet - Copy")
        .Range("D455006", .Range("D502750").End(xlUp)).Copy Destination:=Range("D1")
    With Sheets("BigDataSet - Copy")
        .Range("E455006", .Range("E502750").End(xlUp)).Copy Destination:=Range("E1")
    With Sheets("BigDataSet - Copy")
        .Range("F455006", .Range("F502750").End(xlUp)).Copy Destination:=Range("F1")
    With Sheets("BigDataSet - Copy")
        .Range("G455006", .Range("G502750").End(xlUp)).Copy Destination:=Range("G1")
    With Sheets("BigDataSet - Copy")
        .Range("H455006", .Range("H502750").End(xlUp)).Copy Destination:=Range("H1")
    With Sheets("BigDataSet - Copy")
        .Range("I455006", .Range("I502750").End(xlUp)).Copy Destination:=Range("I1")
    With Sheets("BigDataSet - Copy")
        .Range("J455006", .Range("J502750").End(xlUp)).Copy Destination:=Range("J1")
    End With


End Sub

当我尝试运行它时，我收到一条错误消息，提示“期望结束于”。该脚本的目标是复制行范围并将它们放入新的单独工作表中(然后我可以将其放入单独的文件中，这些文件将在 Excel 2003 中读取而不会超过最大行数)。是否应该在其中的某处添加一个或多个 End With 语句？

Answer 1

您不需要多次重复 With 部分。

With Sheets("BigDataSet - Copy")
  .Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
  .Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
  .Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
  ' Etc
End With

有关“With”关键字，请参阅 MSDN 文档:

http://msdn.microsoft.com/en-us/library/wc500chb(v=vs.100).aspx