vba 如何仅从范围中复制excel vba中的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24294923/
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 only values in excel vba from a range?
提问by pablo.vix
I'm trying to copy values from a table to a Range, in Excel using vba Macros, but I dont want the table format, only its values. How can I achieve this?
我正在尝试使用 vba 宏在 Excel 中将值从表复制到范围，但我不想要表格式，只想要它的值。我怎样才能做到这一点？
Here is part of the code:
这是代码的一部分：
'Source range
Set r = Sheets("Sheet1").Range("Table1")
'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
r.Copy Destination:= dest
采纳答案by pablo.vix
I achieve a solution that works.
我实现了一个有效的解决方案。
There follows the code:
代码如下：
Set r = Sheets("Criteria").Range("CriteriaTable")
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
Sheets("Criteria").Activate
r.Select
Selection.Copy
Sheets("Load").Activate
dest.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
回答by MP24
You can skip the copy command altogether by assigning the values of the source range to your destination range:
您可以通过将源范围的值分配给目标范围来完全跳过复制命令：
'Source range
Set r = Sheets("Sheet1").Range("Table1")
'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
dest.Value = r.Value
回答by Kory
I believe you are looking for the functionality of pasting values. You can record it, or use what I have done below. (from recording so selecting is in there, which will make it run slower, but you aren't looping so it is only constant time being added).
我相信您正在寻找粘贴值的功能。你可以记录下来，或者使用我在下面所做的。（从录音中选择就在那里，这会使它运行得更慢，但你没有循环，所以它只是不断添加时间）。
Selection.Copy
'Select your destination like range("destination").select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
回答by gtwebb
You need to use the pastespecial command as shown below.
您需要使用 pastespecial 命令，如下所示。
'Source range
Set r = Sheets("Sheet1").Range("Table1")
'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
r.Copy
dest.pastespecial paste:=xlPasteValues
回答by pbou
I assume you want to copy from "Sheet1" to "Sheet1" - of course you can make this a parameter and loop through all your sheets
我假设您想从“Sheet1”复制到“Sheet1”-当然您可以将其设为参数并遍历所有工作表
Dim rSource as range 'Source Range
Dim rDest as range 'Target Range - It should be the same dimension
Dim wbSource as workbook 'Source Workbook
Dim wbTarget as workbook 'Target Workbook
Dim myRange as string
myRange = "A:G" ' It is an example, you can make it dynamic
'add new workbook
Workbooks.Add
Set wbTarget = ActiveWorkbook
'Set the Source Range
Set rSource = wbSource.Sheets("Sheet1").Range(myRange)
'Destination Range
Set rDest = wbTarget.Sheets("Sheet1").Range(myRange)
'Copy values only
rSource.Copy
rDest.PasteSpecial xlPasteValues
回答by RHH1095
Use the Range.Value method. Its like setting a variable a = 1. Where you think of it as copying 1 to a. So...
使用 Range.Value 方法。它就像设置一个variable a = 1. 您将其视为将 1 复制到 a 的地方。所以...
Range2.value = Range1.value
or
或者
Worksheets("historical").Range("A1:F15").Value = Worksheets("actuals").Range("A1:F15").Value
Here I'm copying some data in worksheet actual to some historical worksheet. Or if you prefer settingthe value of one range to another range.
在这里，我将实际工作表中的一些数据复制到一些历史工作表中。或者，如果您更喜欢将一个范围的值设置为另一个范围。
回答by user2649602
r.Copy
dest.pastespecial xlPastevalues
