Excel VBA 仅复制粘贴值（ xlPasteValues ）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23937262/
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
Excel VBA Copy Paste Values only( xlPasteValues )
提问by sam
I'm trying to copy entire column in sheetA to Sheet B. sheetA column has values formed with formuls. I'm copying SheetA column values only using xlPasteValues. But it is not paste the values to another sheetB. The column in sheetB is empty. My VBA Code
我正在尝试将 sheetA 中的整个列复制到 Sheet B。 sheetA 列具有用公式形成的值。我仅使用xlPasteValues复制 SheetA 列值。但它不会将值粘贴到另一个 sheetB。sheetB 中的列是空的。我的 VBA 代码
Public Sub CopyrangeA()
Dim firstrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
firstrowDB = 1
arr1 = Array("BJ", "BK")
arr2 = Array("A", "B")
For i = LBound(arr1) To UBound(arr1)
With Sheets("SheetA")
lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
.Range(.Cells(1, arr1(i)), .Cells(lastrow, arr1(i))).Copy
Sheets("SheetB").Range(arr2(i) & firstrowDB).PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub
回答by OSUZorba
If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this:
如果您只想复制整列，您可以通过执行以下操作来大大简化代码：
Sub CopyCol()
Sheets("Sheet1").Columns(1).Copy
Sheets("Sheet2").Columns(2).PasteSpecial xlPasteValues
End Sub
Or
或者
Sub CopyCol()
Sheets("Sheet1").Columns("A").Copy
Sheets("Sheet2").Columns("B").PasteSpecial xlPasteValues
End Sub
Or if you want to keep the loop
或者如果你想保持循环
Public Sub CopyrangeA()
Dim firstrowDB As Long, lastrow As Long
Dim arr1, arr2, i As Integer
firstrowDB = 1
arr1 = Array("BJ", "BK")
arr2 = Array("A", "B")
For i = LBound(arr1) To UBound(arr1)
Sheets("Sheet1").Columns(arr1(i)).Copy
Sheets("Sheet2").Columns(arr2(i)).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False
End Sub
回答by robotik
since you only want values copied, you can pass the values of arr1 directly to arr2 and avoid copy/paste. code inside the loop:
由于您只想复制值，因此您可以将 arr1 的值直接传递给 arr2 并避免复制/粘贴。循环内的代码：
Sheets("SheetB").Range(arr2(i) & firstrowDB).Resize(lastrow, 1).Value = .Range(.Cells(1, arr1(i)), .Cells(lastrow, arr1(i))).Value
回答by bmgh1985
Personally, I would shorten it a touch too if all you need is the columns:
就个人而言，如果您只需要列，我也会稍微缩短一下：
For i = LBound(arr1) To UBound(arr1)
Sheets("SheetA").Columns(arr1(i)).Copy
Sheets("SheetB").Columns(arr2(i)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Next
as from this code snippet, there isnt much point in lastrowor firstrowDB
从这个代码片段来看，lastrow或firstrowDB
回答by Blake Turner
I've had this problem before too and I think I found the answer.
我以前也遇到过这个问题，我想我找到了答案。
If you are using a button to run the macro, it is likely linked to a different macro, perhaps a save as version of what you are currently working in and you might not even realize it. Try running the macro directly from VBA (F5) instead of running it with the button. My guess is that will work. You just have to reassign the macro on the button to the one you actually want to run.
如果您使用按钮来运行宏，它可能链接到不同的宏，可能是您当前使用的另存为版本，您甚至可能没有意识到。尝试直接从 VBA (F5) 运行宏，而不是使用按钮运行它。我的猜测是这会奏效。您只需要将按钮上的宏重新分配给您实际想要运行的宏。
回答by nishit dey
You may use this too
你也可以使用这个
Sub CopyPaste()
Sheet1.Range("A:A").Copy
Sheet2.Activate
col = 1
Do Until Sheet2.Cells(1, col) = ""
col = col + 1
Loop
Sheet2.Cells(1, col).PasteSpecial xlPasteValues
End Sub
回答by akallali
you may use this:
你可以使用这个：
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
回答by HARSHUL
selection=selection.values
selection=selection.values
this do things at a very fast way.
这以非常快的方式做事。
