paste special in vba

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

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

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

Add ".Range("A1")." Between 'ActiveSheet' and 'PasteSpecial' Change A1 to the location you want to paste to.