vba 使用范围，如何将 .COPY DESTINATION 粘贴为 VALUES
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12821844/
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
Using ranges, how can I make a .COPY DESTINATION paste as VALUES
提问by aSystemOverload
I have the following code, which works just fine to copy the entire cells to their new destination.
我有以下代码，它可以很好地将整个单元格复制到新的目的地。
Dim ws As Worksheet, rng As Range
Set ws = Sheets("Duplicates")
Set rng = ws.Range("A2")
rng.Copy Destination:=Range("A2:A" & Range("G" & Rows.Count).End(xlUp).Row)
- Is it possible to make that cope a
paste special. - Or do I need to split the copy/paste into two lines using a two range objects?
- 有没有可能让那个应付一个
paste special. - 或者我是否需要使用两个范围对象将复制/粘贴分成两行？
EDIT:
编辑：
Right, we've concluded that you CANNOT have a paste special on the same line as a copy. What I've also found is that you may not wish to anyway.
是的，我们已经得出结论，您不能在与副本相同的行上添加特殊粘贴。我还发现你可能不希望这样做。
If you copy a formula from Record 1 and paste it to all the records as VALUES, you will get the formula's value for the first record copied down.
如果您从记录 1 复制一个公式并将其粘贴到所有记录中VALUES，您将获得向下复制的第一条记录的公式值。
This is not what I wanted. So you have to copy the formulae down to all the records as a formulae, then copy those formula and paste as values.
这不是我想要的。因此，您必须将公式作为公式复制到所有记录，然后复制这些公式并粘贴为值。
Dim ws As Worksheet, rng As Range
Set ws = Sheets("Duplicates")
Set up first range (single cell with formula) and copy/paste the contents down to the bottom of the spreadsheet based on Col G:
设置第一个范围（带有公式的单个单元格）并根据 Col 将内容复制/粘贴到电子表格的底部G：
Set rng = ws.Range("A2")
rng.Copy Destination:=Range("A2:A" & Range("G" & Rows.Count).End(xlUp).Row)
Select the freshly pasted formule, from the second row down. Copy them. Then paste the contents as values to the same place.
从第二行开始选择新粘贴的公式。复制它们。然后将内容作为值粘贴到同一位置。
Set rng = ws.Range("A3:A" & Range("G" & Rows.Count).End(xlUp).Row)
rng.Copy
rng.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
回答by Erik Eidt
Alternatively, you can just assign range values without using copy & paste:
或者，您可以只分配范围值而不使用复制和粘贴：
Set r1 = Range("D1", "D7")
Set r2 = Range("E1", "E7")
r2.Value = r1.Value ' copies current values from one range to another
after this assignment, the r2 range will have values not formulas even if r1 has forumlas.
在此分配之后，即使 r1 具有论坛，r2 范围也将具有值而不是公式。
In your case, perhaps:
在你的情况下，也许：
rng.Value = rng.Value
(once you've set rng to the correct size as you've noted)
（一旦您将 rng 设置为您注意到的正确大小）
