vba 使用可变单元格引用将范围从一张纸复制到另一张纸

Copying ranges from one sheet to another using variable cell references

提问by Angus 'Burger' Clark

I am trying to copy one range to another via vba in excel, however it refuses to copy anything. This is my code:

我试图通过 excel 中的 vba 将一个范围复制到另一个范围，但是它拒绝复制任何内容。这是我的代码：

Worksheets("SEARCH SHEET").Range(Cells(destination_y, 1), Cells(destination_y, 25)).Value
 = Worksheets("STOCK-INDIV").Range(Cells(currentItem_y, 1), Cells(currentItem_y, 25)).Value

and at previous point in the code I state:

在代码的前一点我声明：

currentItem_y = ActiveCell.Row

and

和

destination_y = ActiveCell.Row

I know the destination and current references are correct, and by doing

我知道目的地和当前参考是正确的，并且通过这样做

Worksheets("SEARCH SHEET").Range(Cells(destination_y, 1), Cells(destination_y, 25)).Select

I know that the correct cells are set to be copied and pasted to.

我知道正确的单元格被设置为复制和粘贴到。

回答by David Zemens

You need to qualify the Cells as belonging to a specific worksheet, otherwise it's assumed to use the ActiveSheet, which is not what you want.

您需要将单元格限定为属于特定工作表，否则假定使用 ActiveSheet，这不是您想要的。

My preference is to use more object-oriented programming, it will be easier to do qualify the Cellsportions of the Rangeobjects. So your code :

我的偏好是使用更多的面向对象编程，它会更容易地限定对象的Cells部分Range。所以你的代码：

Worksheets("SEARCH SHEET").Range(Cells(destination_y, 1), Cells(destination_y, 25)).Value
 = Worksheets("STOCK-INDIV").Range(Cells(currentItem_y, 1), Cells(currentItem_y, 25)).Value

Make sure that the values of destination_yand currentItem_yare correct (I would like to revise to avoid using ActiveCellreference), and then you can modify your code to something like:

确保的价值观destination_y和currentItem_y正确（我想修改，以避免使用ActiveCell参考），然后你可以修改你的代码是这样的：

Dim wsSearch As Worksheet
Dim wsStock As Worksheet
Dim sourceRange as Range
Dim destRange as Range

'## Define worksheet object variables
Set wsSearch = Worksheets("SEARCH SHEET")
Set wsStock = Worksheets("STOCK-INDIV")

With wsSearch
    '## Explicitly define a range object on this Worksheet
    Set destRange = .Range(.Cells(destination_y,1), .Cells(destination_y,25))
End With
With wsStock
   '## Explicitly define a range object on this Worksheet
    Set sourceRange = .Range(.Cells(currentItem_y, 1), .Cells(currentItem_y, 25))
End With

'## Transfer values from sourceRange to destRange
destRange.Value = sourceRange.Value