在 vba 中粘贴特殊
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6343107/
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
paste special in vba
提问by mehak
i am trying to use pastespecial in vba..i basically need to paste the values (and not the formulas as the formula gets recalculated while pasting to the new sheet because of change in the cell values in that sheet) to another sheet...But i am getting error 1004 saying 'aaplication defined or object defined error'..heres the code...please help somebdy...
我正在尝试在 vba 中使用 pastespecial ..我基本上需要将值（而不是公式，因为公式在粘贴到新工作表时重新计算，因为该工作表中的单元格值发生变化）到另一个工作表......但我收到错误 1004 说“aaplication 定义或对象定义错误”..继承人的代码...请帮助somebdy...
Sub Macro1try()
Dim i As Integer
Dim j As Integer
For i = 1 To 2
Worksheets("Volatility").Cells(1, "B").Value = Worksheets("Volatility").Cells(i, "S").Value
Call mdlMain.ExtractData
Range("A11:D2330").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Sheets("Volatility").Activate
Next i
End Sub
回答by Jean-Fran?ois Corbett
This I learned the hard way: Avoid Copy/Paste if at all possible!Copy and Paste use the clipboard. Other programs may read from / write to the clipboard while your code is running, which will cause wild, unpredictable results at both ends.
这是我通过艰难的方式学到的：尽可能避免复制/粘贴！复制和粘贴使用剪贴板。其他程序可能会在您的代码运行时从剪贴板读取/写入剪贴板，这将导致两端出现疯狂的、不可预测的结果。
In your particular case, Copy and Paste are completely unnecessary. Just use =.
在您的特定情况下，复制和粘贴完全没有必要。只需使用=.
For i = 1 To 2
'// Your stuff, which I won't touch:
Worksheets("Volatility").Cells(1, "B").Value _
= Worksheets("Volatility").Cells(i, "S").Value
Call mdlMain.ExtractData
Sheets.Add After:=Sheets(Sheets.Count)
'// The following single statement replaces everything else:
Sheets(Sheets.Count).Range("A11:D2330").Value _
= Sheets("Volatility").Range("A11:D2330").Value
'// Voilà. No copy, no paste, no trouble.
'// If you need the number format as well, then:
Sheets(Sheets.Count).Range("A11:D2330").NumberFormat_
= Sheets("Volatility").Range("A11:D2330").NumberFormat
Next i
回答by osknows
You need to state where you're putting it on the sheet
你需要说明你把它放在工作表上的什么地方
Sub Macro1try()
Dim i As Integer
Dim j As Integer
For i = 1 To 2
Worksheets("Volatility").Cells(1, "B").Value = Worksheets("Volatility").Cells(i, "S").Value
Call mdlMain.ExtractData
Sheets.Add After:=Sheets(Sheets.Count)
Worksheets("Volatility").Range("A11:D2330").Copy
Sheets(Sheets.Count).Range("A11:D2330").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next i
End Sub
回答by tonypreece
Add ".Range("A1")." Between 'ActiveSheet' and 'PasteSpecial' Change A1 to the location you want to paste to.
添加“.Range（“A1”）。” 在“ActiveSheet”和“PasteSpecial”之间将 A1 更改为要粘贴到的位置。
