vba 粘贴值时如何引用非活动工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19351494/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I refer to an inactive sheet when doing a paste values?
提问by user2877197
I am new to VBA in Excel. I am trying to write a code that pastes the value of a cell into another cell and then autofills to a specific range in the same column. How can I do this in the background without the sheet being active? This is the code that the Macro Recorder wrote:
我是 Excel 中 VBA 的新手。我正在尝试编写一个代码，将一个单元格的值粘贴到另一个单元格中，然后自动填充到同一列中的特定范围。如何在后台执行此操作而不激活工作表？这是宏记录器编写的代码：
Sheets("Results").Select
Range("D8").Select
Selection.Copy
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("E2:E8"), Type:=xlFillDefault
Range("E2:E8").Select
Selection.AutoFill Destination:=Range("E2:E44"), Type:=xlFillDefault
Range("E2:E44").Select
Columns("E:E").EntireColumn.AutoFit
回答by stobin
You can specify an inactive workbook or worksheet by using something like this: Workbooks("Book1").Sheets("Sheet1").
您可以通过使用像这样指定一个非活动工作簿或工作：Workbooks("Book1").Sheets("Sheet1")。
For example:
例如：
Workbooks("Book1").Sheets("Sheet1").Range("D8").Copy
Workbooks("Book1").Sheets("Sheet2").Range("E8").PasteSpecial Paste:=xlPasteValues
回答by David Zemens
Work with range objects, explicitly defined to their parent worksheets (and possibly Workbooks, if needed):
使用范围对象，明确定义到其父工作表（如果需要，可能还有工作簿）：
Dim copyRange as Range
Dim destRange as Range
Set copyRange = Sheets("Results").Range("D8") 'Or Workbooks("Book1").Sheets("SheetName").Range("D8")
Set destRange = Sheets("Other Sheet").Range("E8") 'Or Workbooks("Book2").Sheets("Another Sheet").Range("E8")
copyRange.copy
destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
destRange.AutoFill destRange.Offset(-7, 0).Resize(8, 1), xlFillDefault
destRange.AutoFill destRange.Resize(36, 1), xlFillDefault
destRange.EntireColumn.Autofit
It was not clear to me when/if you changed worksheets during the macro recorder session, and it wasn't clear to me the various selections/autofills you were doing. Let me know if you need help figuring those parts out, I took a guess at it above.
我不清楚您何时/是否在宏记录器会话期间更改了工作表，并且我不清楚您正在执行的各种选择/自动填充。如果您需要帮助弄清楚这些部分，请告诉我，我在上面进行了猜测。
Here is another method you could probably use:
这是您可能会使用的另一种方法：
Dim copyRange as Range
Dim destRange as Range
Dim dt as Date
Set copyRange = Sheets("Results").Range("D8")
Set destRange = Sheets("Other Sheet").Range("E2")
dt = DateAdd("d", -6, DateValue(copyRange.Value)) 'get the value 6 days before
copyRange.Copy destRange 'copy the formatting/etc
destRange.Value = dt 'insert the value
destRange.AutoFill destRange.Resize(44, 1), xlFillDefault
destRange.EntireColumn.Autofit
