excel - 在 Excel 中复制范围时出现运行时错误 6

Question

我正在创建一个宏来将多个工作表连接成一个。由于每个工作表都有不同数量的行，我尝试使用 .end(xlDown) 对其进行动态设置。目前，子遇到很多错误:最初它们是对象错误，当我修复它们时，我得到了堆栈溢出运行时错误 6。将变量从整数更改为 Long 并没有解决问题，现在我只得到一个“400"错误。

这是代码:

Sub Concatenate()
    'Declare Variables
    Dim Curwb As Workbook

    'Set Variables
    Set Curwb = ActiveWorkbook

       'Concatenate Data
       'Timestamps
        Dim Stage1Count As Long
        Dim Stage2Count As Long
        Dim Stage3Count As Long
        Dim Stage4Count As Long
        Dim Stage5Count As Long
        Dim TotalCount As Long
            'Stage 1
                Curwb.Sheets("Stage 1").Select
                If Range("A3").End(xlDown).Address = Range("A3").Address Then
                    Range("A3").Copy Destination:=Curwb.Sheets _
("CombinedData").Range("A3")
                Else
                Range("A3", Range("A3").End(xlDown)).Select
                    Stage1Count = Selection.Cells.Count
                    Selection.Copy Destination:=Curwb.Sheets _
("Combined Data").Range("A3")
                End If
            'Stage 2
                Curwb.Sheets("Stage 2").Select
                If Range("A3").End(xlDown).Address = Range("A3").Address Then
                    Range("A3").Copy Destination:=Curwb.Sheets _
("Combined Data").Range("A3").Offset(Stage1Count, 0)
                Else
                Range("A3", Range("A3").End(xlDown)).Select
                    Stage2Count = Selection.Cells.Count
                    Selection.Copy Destination:=Curwb.Sheets _
("Combined Data").Range("A3").Offset(Stage1Count, 0)
                End If
End Sub

我的代码有什么问题？

Answer 1

有几点可以帮助:

1) 避免使用.Select , ActiveWorkbook , ActiveSheet等等

2) 而不是使用 .End(xlDown) ，如果您转到最后一行并使用 .End(xlUp)它将更有效地获得您最后使用的行。

3) 使用已声明为变量的工作表也更容易。

我已经对您的代码进行了调整，这大大缩短了它，应该可以让您完成您所追求的。

Sub Concatenate()

'Declare Variables
Dim Curwb As Workbook

'Set Variables
Set Curwb = Workbooks("NameOfWorkbook") 'Avoid ActiveWorkbook at all cost :)

Dim ws As Worksheet, wsCopyTo As Worksheet
Dim rng As Range

With Curwb

    Set ws = .Sheets("Stage 1")
    Set wsCopyTo = .Sheets("Combined Data")

    With ws

        Set rng = .Range(.Range("A3"), .Range("A" & .Rows.Count).End(xlUp))
        rng.Copy wsCopyTo.Range("A" & wsCopyTo.Rows.Count).End(xlUp).Offset(1)

    End With

    Set ws = .Sheets("Stage 2")
    With ws

        Set rng = .Range(.Range("A3"), .Range("A" & .Rows.Count).End(xlUp))
        rng.Copy wsCopyTo.Range("A" & wsCopyTo.Rows.Count).End(xlUp).Offset(1)

    End With

End Sub