尽量避免使用床单。复制数据时激活

Running on Excel 2013 and I'm stumped. I'm trying to avoid having to activate the worksheet (let alone multiple lines of code) just to copy the data. Why would this work:

在Excel 2013上运行，我被难住了。我尽量避免仅仅为了拷贝数据而激活工作表(更不用说多行代码)。为什么这项工作:

Sheets("DataDump").Activate
Range(Cells(startJobRow, 1), Cells(lastJobRow, 5)).Copy Sheets("DataRaw").Cells(lastDataRawRow + 1, 1)

But this does not:

但这并不是:

Sheets("DataDump").Range(Cells(startJobRow, 1), Cells(lastJobRow, 5)).Copy Sheets("DataRaw").Cells(lastDataRawRow + 1, 1)

The second code gives me "Run-time error '1004': Application-defined or object-defined error". If I manually select the worksheet "DataDump" while in debug mode and tell it to continue then it works great.

第二个代码给出“运行时错误‘1004’:应用程序定义的或对象定义的错误”。如果我在调试模式中手动选择工作表“DataDump”并告诉它继续，那么它就会工作得很好。

1 个解决方案

#1

In the second example your use of Cells() is not qualified and this can cause problems.

在第二个示例中，单元格()的使用是不合格的，这会导致问题。

In this case Cells() is referencing whichever sheet is active at the instant the line of code is evaluated.

在这种情况下，单元格()引用的是在计算代码行时活动的任何表。

You need to fully qualify Cells() in this case.

在这种情况下，您需要完全限定单元格()。

This should do it:

这应该这样做:

With Sheets("DataDump")
    .Range(.Cells(startJobRow, 1), .Cells(lastJobRow, 5)).Copy Sheets("DataRaw").Cells(lastDataRawRow + 1, 1)
End With

#1