vba 如何在excel中只粘贴值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14135560/
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 paste only values in excel
提问by maldivianGeek
I'm quite new to Excel and completely new to this forum.
我对 Excel 很陌生，对这个论坛完全陌生。
I have taken the code from the below forum and modified it to my need.
我已经从下面的论坛中获取了代码并根据我的需要对其进行了修改。
Sub copyTotals()
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceRow As Integer, SourceCells As Range
Set SourceSht = ThisWorkbook.Sheets("DUN - Jan")
Set TargetSht = ThisWorkbook.Sheets("DUN Jan - Jan,Feb,Mar,Apr")
Set SourceCells = SourceSht.Range("L36,N36")
If TargetSht.Range("C11").Value = "" Then
SourceRow = 1
ElseIf TargetSht.Range("C41") <> "" Then
MsgBox ("The sheet is full, you need to create a new sheet")
Else
SourceRow = TargetSht.Range("C41").End(xlUp).Row + 1
End If
SourceCells.Copy TargetSht.Cells(SourceRow, 3)
End Sub
The problem is that the values pasted have the formating of the source and i only want to paste the values.
问题是粘贴的值具有源的格式，我只想粘贴这些值。
Can someone please help me with this.
有人可以帮我解决这个问题。
回答by Dan
Use .Copy together with .PasteSpecial. Instead of:
将 .Copy 与 .PasteSpecial 一起使用。代替：
SourceCells.Copy TargetSht.Cells(2, SourceCol)
Do this:
做这个：
SourceCells.Copy
TargetSht.Cells(2, SourceCol).PasteSpecial xlPasteValues
回答by tony goodwin
Using the macro recorder yields this kind of thing. I use it a lot when stuck.
使用宏记录器会产生这种情况。卡住时我经常使用它。
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
回答by SJC
You don't need to copy and paste to do this (at least in Excel 2010 and above, I think). You just set the .Value of the range to equal itself. eg:
您不需要复制和粘贴来执行此操作（我认为至少在 Excel 2010 及更高版本中）。您只需将范围的 .Value 设置为等于自身。例如：
rng.Value = rng.Value
or
或者
Sheet1.Range("A1:A10").Value = Sheet1.Range("A1:A10").Value
since .Value "Returns or sets a Variant value that represents the value of the specified range."
因为 .Value “返回或设置一个表示指定范围值的 Variant 值。”
Note that this just works for values, not formats.
请注意，这仅适用于值，而不适用于格式。
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
回答by ufosnowcat
right click in the cell and select paste special then you can choose values only
右键单击单元格并选择特殊粘贴，然后您只能选择值
edit: for macros its the same principle use .PasteSpecial xlPasteValues like here
编辑：对于宏，其原理相同，使用 .PasteSpecial xlPasteValues 就像这里
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")
