vba 如何在不使用激活表的情况下复制粘贴目标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23143254/
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 to copy paste destination without using activate sheet
提问by user3546835
I'm having a problem with my macro, I run it from one sheet(A) and I need to copy cells from sheet(B) and paste it to sheet(C) but I could only make it changing sheets, there is any way to do that without activate sheets???
我的宏有问题，我从一张工作表（A）运行它，我需要从工作表（B）复制单元格并将其粘贴到工作表（C），但我只能让它改变工作表，有任何没有激活表的方法？？？
My macro:
我的宏：
enter code here
Sub VPL()
Dim j As Long, i As Long
For j = 1 To 9
' COPIAR/COLAR GERA??O
' How to do this without activate sheets "Gera??o" and "Premissas"????
Sheets("Gera??o").Activate
ActiveSheet.Range(Cells(20, 2 + j), Cells(31, 2 + j)).Select
Selection.Copy
Sheets("Premissas").Activate
ActiveSheet.Range("Z20:AI31").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Premissas").Activate
ActiveSheet.Range("AL20:AL31").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
' COPIAR/COLAR PLD
For i = 1 To 2000
'The same problem here.
Sheets("PLD NE").Activate
ActiveSheet.Range(Cells(1 + i, 1), Cells(1 + i, 60)).Select
Selection.Copy
Sheets("Macro").Activate
ActiveSheet.Range("AZ27").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
'COPIAR/COLAR VPL
'The macro runs form Sheets("RESULTADO LEN") and I want only to be on this sheet.
Sheets("RESULTADO LEN").Activate
Sheets(10).Cells(3 + i, 2 + j) = Sheets(5).Cells(35, 4).Value 'Ametista
Sheets(10).Cells(3 + i, 11 + j) = Sheets(5).Cells(62, 4).Value 'Borgo
Sheets(10).Cells(3 + i, 20 + j) = Sheets(5).Cells(89, 4).Value 'Caitite
Sheets(10).Cells(3 + i, 29 + j) = Sheets(5).Cells(116, 4).Value 'Dourados
Sheets(10).Cells(3 + i, 38 + j) = Sheets(5).Cells(143, 4).Value 'Espig?o
Sheets(10).Cells(3 + i, 47 + j) = Sheets(5).Cells(170, 4).Value 'Maron
Sheets(10).Cells(3 + i, 56 + j) = Sheets(5).Cells(197, 4).Value 'Pelourinho
Sheets(10).Cells(3 + i, 65 + j) = Sheets(5).Cells(224, 4).Value 'Pil?es
Sheets(10).Cells(3 + i, 74 + j) = Sheets(5).Cells(251, 4).Value 'Serra do espig?o
Sheets(10).Cells(3 + i, 83 + j) = Sheets(6).Cells(36, 4).Value 'S?o Salvador
Next
Next
End Sub
回答by surfmuggle
In the linked question above i found the Range.Copy command.
在上面的链接问题中，我找到了 Range.Copy 命令。
You can use it like this:
你可以这样使用它：
Sub CopyCells()
Dim RngA As Range
Set RngA = Sheets("A").Range("B1:C4")
Sheets(1).Cells(1, 1).Value = "foo"
RngA.Copy Sheets(1).Range("B1:C4")
End Sub
回答by Tim Williams
(untested)
（未经测试）
With no selecting/activating:
没有选择/激活：
Sub VPL()
Dim j As Long, i As Long, rng As Range
Dim rw As Range, col As Range
Dim wb As Workbook
Set wb = ActiveWorkbook
For j = 1 To 9
With wb.Sheets("Gera??o")
Set rng = .Range(.Cells(20, 2 + j), .Cells(31, 2 + j)).Copy
End With
wb.Sheets("Premissas").Range("Z20:AI31,AL20:AL31").Value = rng.Value
For i = 1 To 2000
With wb.Sheets("PLD NE")
Set rng = .Range(.Cells(1 + i, 1), .Cells(1 + i, 60))
End With
wb.Sheets("Macro").Range("AZ27").Resize(rng.Rows.Count, _
rng.Columns.Count).Value = rng.Value
wb.Sheets("RESULTADO LEN").Activate
Set rw = wb.Sheets(10).Rows(3 + i)
Set col = wb.Sheets(5).Columns(4)
With rw
.Cells(2 + j).Value = col.Cells(35).Value 'Ametista
.Cells(11 + j) = col.Cells(62).Value 'Borgo
.Cells(20 + j) = col.Cells(89).Value 'Caitite
'...etc
End With
Next i
Next j
End Sub
