在VBA中特殊粘贴

发布于 2024-11-15 03:18:35 字数 655 浏览 3 评论 0原文

我正在尝试在vba中使用pastespecial..我基本上需要将值（而不是公式，因为在粘贴到新工作表时由于该工作表中的单元格值发生变化而重新计算公式）到另一个工作表...但我收到错误 1004 说“aaplication 定义或对象定义错误”..这是代码...请帮助某人...

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

原文

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...

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

日暮斜阳 2024-11-22 03:18:35

我通过惨痛的教训学到了这一点：尽可能避免复制/粘贴！复制和粘贴使用剪贴板。当您的代码运行时，其他程序可能会读取/写入剪贴板，这将在两端导致疯狂的、不可预测的结果。

在您的特定情况下，复制和粘贴是完全没有必要的。只需使用=即可。

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

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

回复收藏 0 原文

静赏你的温柔 2024-11-22 03:18:35

您需要说明将其放在表格上的位置

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

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

回复收藏 0 原文