VBA复制和粘贴仅在我激活工作表后才有效 [英] VBA copy and paste only work if I activate the sheet

问题描述

我正在工作表之间复制一些范围，但我不知道为什么只有在激活工作表后才能复制或粘贴它们，该范围才起作用。起作用：

I am copying some ranges between sheets and I don't know why it only works when I activate the sheet before I copy or paste them. This works:

s.Activate
s.Range(Cells(2, 8), Cells(lrow, 8)).Copy
d.Activate
d.Range(Cells(2, 3), Cells(lrow, 3)).PasteSpecial xlPasteValues

这不是：

s.Range(Cells(2, 8), Cells(lrow, 8)).Copy
d.Range(Cells(2, 3), Cells(lrow, 3)).PasteSpecial xlPasteValues

如果我已经用变量 s和 d指定了图纸，为什么VBA需要激活图纸？谢谢

Why does VBA need the sheets to be activated if I've already specified them with the variables "s" and "d"? Thanks

推荐答案

这是因为当 Range（...）和 Cells（...）方法不合格，它们在 Active Worksheet 上运行。

That's because when Range(...) and Cells(...) methods are not qualified, they operate on the Active Worksheet.

s.Activate

s.Activate

s.Range（Cells（2，8），Cells（lrow，8））。Copy

s.Range(Cells(2, 8), Cells(lrow, 8)).Copy

如果 s 不是活动工作表，则您需要从 s 请求一个范围不属于它的单元格，而是属于另一个 active 工作表的单元格。因为

If s was not the active sheet, you would be requesting from s a range of cells that do not belong to it, but to the other, active worksheet. Because

       Cells(2, 8)     <==>   ActiveSheet.Cells(2, 8) 
       Cells(lrow, 8)  <==>   ActiveSheet.Cells(lrow, 8) 

为避免此麻烦，请始终限定范围并单元格：

To avoid this trouble, always qualify your ranges and cells:

 s.Range(s.Cells(2, 8), s.Cells(lrow, 8)).Copy

这是正确的方法；每当您发现自己使用诸如激活和选择之类的东西时，请了解您最有可能做错了什么。在好的实践中，您几乎永远不需要使用这些东西。

This is the correct approach; whenever you find yourself using stuff like Activate and Select, understand that you are most-likely doing something wrong. In good practice you will almost never need to use this stuff.

这篇关于VBA复制和粘贴仅在我激活工作表后才有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！