excel - VBA代码不能按顺序工作是有原因的吗？(Is there a reason why VBA code does not work in sequential order?)

Question

I am not entirely sure how to phrase this question.

(我不确定如何表达这个问题。)

However, I will be able to explain it better here.

(但是，我将能够在这里更好地解释它。)

Below is my code.

(下面是我的代码。)

The purpose of my code is to copy and paste data from one sheet to another.

(我的代码的目的是将数据从一张纸复制并粘贴到另一张纸。)

Everything seems to work fine up until it gets to the very last line of code (excluding "End Sub").

(一切工作正常，直到到达最后一行代码（“ End Sub”除外）为止。)

The last line is supposed to fill down to the last row.

(最后一行应该填充到最后一行。)

The issue I am getting is that the code works fine if I break up the With statement and the final line and run them separately.

(我遇到的问题是，如果我分开With语句和最后一行并分别运行它们，则代码可以正常工作。)

I know the last line works but when I run the entire macro, I get a "Run time Error '1004" error message.

(我知道最后一行有效，但是当我运行整个宏时，会收到“运行时错误'1004”错误消息。)

Why does my code not work?

(为什么我的代码不起作用？)

Sub Data_Table()

Dim Data As Worksheet
Dim Sum As Worksheet
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim lr4 As Long
Dim lr5 As Long


Set Data = Worksheets("Data-Tracker")
Set Sum = Worksheets("Summary")
lr = Data.Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Data.Cells(Rows.Count, "A").End(xlUp).Row 'for customer type
lr3 = Data.Cells(Rows.Count, "B").End(xlUp).Row ' for Type
lr4 = Data.Cells(Rows.Count, "C").End(xlUp).Row ' for Rate/Budget
lr5 = Data.Cells(Rows.Count, "D").End(xlUp).Row ' for Date


    With Sum

        .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
        .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
        .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
        .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
        .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
        .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)


    End With

    Data.Range("B" & lr3, "D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)


End Sub

Any help would be greatly appreciated.

(任何帮助将不胜感激。)

EDIT :

(编辑 ：)

To help further explain my point, if I first run my code like this:

(为了帮助进一步解释我的观点，如果我首先运行如下代码：)

    With Sum

        .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
        .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
        .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
        .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
        .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
        .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)


    End With

'    Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)

And then after run it like:

(然后运行后，如下所示：)

'    With Sum
'
'        .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
'        .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
'        .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
'        .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
'        .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
'        .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
'
'
'    End With

    Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)

I have to add the apostrophes in to cancel the code out in order for it to work.

(我必须添加撇号以取消代码以使其正常工作。)

Otherwise I get a Autofill Method error.

(否则会出现自动填充方法错误。)

  ask by GSC translate from so

Answer 1

There are two things you need to do.

(您需要做两件事。)

The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ... lr3 & ":D" ... and not this ... lr3, "D" ...

(第一个也是很明显的一个问题是在相关行中修复语法：在源和目标中都需要指定一个范围地址，而不是在角落处：... lr3 & ":D" ...而不是this。 .. lr3, "D" ...)

The second one is to make sure that the Destination is always taller than the source, just in case:

(第二个是确保目标始终比源高，以防万一：)

If lr > lr5 Then
     Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If