使用范围，如何将.COPY DESTINATION粘贴为VALUES [英] Using ranges, how can I make a .COPY DESTINATION paste as VALUES
问题描述
我有以下代码，可以很好地将整个单元格复制到它们的新目的地.
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)
- 是否有可能使这种处理成为
paste special. - 还是我需要使用两个范围将复制/粘贴分为两行对象?
对，我们得出的结论是，您不能在副本的同一行上添加特殊的粘贴.我还发现，您可能还是不希望这样做.
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.
如果您从记录1复制一个公式并将其作为 VALUES 粘贴到所有记录，则将获得复制下来的第一条记录的公式值.
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.
这不是我想要的.因此，您必须将公式作为公式复制到所有记录，然后复制这些公式并粘贴为值.
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")
设置第一个范围(带有公式的单个单元格)，然后根据Col G 将内容复制/粘贴到电子表格的底部:
Set up first range (single cell with formula) and copy/paste the contents down to the bottom of the spreadsheet based on 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
推荐答案
或者，您可以直接分配范围值，而无需使用copy&粘贴:
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
完成此分配后，即使r1具有forumlas，r2范围的值也不会是公式.
after this assignment, the r2 range will have values not formulas even if r1 has forumlas.
在您的情况下，也许:
rng.Value = rng.Value
(一旦您将rng设置为您所记的正确大小)
(once you've set rng to the correct size as you've noted)
这篇关于使用范围，如何将.COPY DESTINATION粘贴为VALUES的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
